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