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),

          (234,'0101'),

          (234,'0201;0301'),

          (567,'0401;0501;0691'),;

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:

   SELECT

      ConstituentID,

      LTRIM(RTRIM(a.b.value('.[1]','VARCHAR(255)'))) Parish

   FROM

     (

      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    http://blog.sqlauthority.com/.