Wednesday, March 16, 2016

Parse a delimited list into multiple records with tSQL

This is great!  I have always used a table-valued function to go in and split a delimited string into multiple values.  Not a bad method, but I learned something today that is much easier!! Check it out:

   -- table variable for the delimited strings
   DECLARE @source TABLE (ConstituentID INT, ParishByConCodes VARCHAR(255))
   INSERT @source (ConstituentID,ParishByConCodes)
   VALUES (123,Null),

Now take a look at what we just wrote: 

   SELECT * FROM @source

Good, good... Now let's split each of those Parish codes out into separate values. Comment out the SELECT from @source, and add this in there, directly after the insert into the table variable:

      LTRIM(RTRIM(a.b.value('.[1]','VARCHAR(255)'))) Parish
      SELECT ConstituentID,CAST('<XMLRoot><RowData>' +   
      REPLACE(ParishByConCodes,';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) x
      FROM  @source)t CROSS APPLY x.nodes('/XMLRoot/RowData')a(b)

Run the whole statement again, and look at our results:

Isn't that the best?!  I certainly think so.  Kudos to Pinal Dave at

No comments:

Post a Comment