SQL create a delimited string from a flat file

2018-05-07T18:34:42+00:00 February 24th, 2015|SQL|

I’ve recently written a post on how to split a delimited string in SQL, now I need to do the reverse and take the flat table and create a delimited string. Fortunately creating a delimited string is far simpler than splitting a delimited string. Assuming we have a flat table called CountryUsers with the following structure: [...]

SQL split string by a delimiter to create new rows

2015-02-04T21:07:40+00:00 February 4th, 2015|SQL|

I’ve just spent a reasonable amount of time digging through the internet to work out how to take a dataset containing a delimited string and then split the string to create new records. For example you could have a table called Users: Country User UK Bob|Fred|Bill USA Mike|Lou|Carol|Sam What you want to do is split [...]

Quickest way to remove the time from a datetime in SQL

2018-05-07T20:22:07+00:00 November 10th, 2011|SQL|

In an earlier post I showed some ways to remove the time portion from a date, but the obvious follow on from that is which is fastest. To test I ran both versions on a dataset of 1.9m rows of data. The DATE datatype returned the records in 15m 35s = 935s Getting the data [...]

Remove a line feed / carriage return from a string

2018-05-07T08:25:35+00:00 August 23rd, 2011|Access, Excel, SQL|

If you ever have to deal with long strings of freetext it’s likely you’ve come across random line feed and carriage returns in that text causing you headaches when it comes to manipulating the text. Once you know how it’s quite a simple problem to deal with. This article describes how to remove the carriage [...]

Link Tableau to SSRS

2018-05-07T19:57:46+00:00 June 7th, 2011|SQL, Tableau|

I’ve had a few visitors coming to the site searching for how to link Tableau to SSRS so I thought I’d write a quick post on how I’ve done it. You need to make use of the URL action in Tableau, which you use in a dashboard. To be wanting to know how to link [...]

SQL First Day of Month

2018-05-07T20:02:52+00:00 May 19th, 2011|SQL|

This post describes how to find the first day of a month in SQL. The code is: DATEADD(mm,DATEDIFF(mm,0,DateToRemoveTime),0) A full explanation of how this works is in a previous post describing how to remove the time portion of a datetime in SQL as the concept is exactly the same except where removing the time uses the [...]

SQL Calculating the row number

2011-05-07T11:58:46+00:00 May 7th, 2011|SQL|

To count the row number in SQL there's a useful little in built SQL function you need to use, row_number() This enables you to number all rows and restart the row numbering part way a dataset. Note this is different to the SQL rank function. This is a very useful thing is you want to [...]

Remove the time from a datetime in SQL

2018-05-07T20:18:57+00:00 April 27th, 2011|SQL|

There are a few ways to remove the time from a datetime in SQL. This article descibes a couple of them. This is the code: DATEADD(dd,0,DATEDIFF(dd,0,DateToRemoveTime)) I'll explain how this works as it was quite confusing initially. Part 1: DATEDIFF(dd,0,DateToRemoveTime) What this is doing is finding the number of whole days (without time) from the [...]