SQL Split function

New improved function available here.

Often when writing a SQL query I want to write something like

SELECT * FROM TableA a
WHERE a.Field1 IN ('string1,string2,string3')

While this syntax is fine for a very small query like the above, it gets a bit messy when there are many possible values. It also doesn’t work so well if you’re not wanting to do an exact match, e.g. if Field1 is of type DateTime and the list is pure dates. The obvious solution was a SQL function equivalent to VBA’s or C#’s Split.

I have to say, this one took a bit of work, and pushed me into dabbling with bits of SQL I wasn’t familiar with, namely querying XML, but the net result is pretty cool. We can now write the above as

SELECT a.* FROM TableA a
JOIN Split(',',
  'string1,
   string2,
   string3') s
ON a.Field1 = s.val

The string1, string2 part doesn’t need to be on separate lines. That just improves readability.

So, here is the function (IMPROVED VERSION HERE):

CREATE FUNCTION dbo.Split(@delimiter VARCHAR(32),
                          @string VARCHAR(MAX))
RETURNS @t TABLE(val VARCHAR(MAX))
AS
BEGIN
    DECLARE @xml XML
    SET @string = REPLACE(
      REPLACE(@string, CHAR(13) + CHAR(10), ''), CHAR(9), '')
    SET @xml = N'<root><r>' + 
       REPLACE(@string, @delimiter, '</r><r>') + '</r></root>'

    INSERT INTO @t(val)
        SELECT RTRIM(LTRIM(r.value('.','varchar(max)'))) as Item
        FROM @xml.nodes('//root/r') as Records(r)

    RETURN
END