DECLARE @XML XML;

SELECT @XML = '<r>'+REPLACE(@Code_String, ',', '</r><r>')+'</r>';

This

1. removes any comma ( , ) character in the input string

2. Replces </r><r> tags for the commas; the first </r> is for the closing tag of the value that lied before each comma and <r> is for the opening tag of the value that lied after each comma

3. appends <r> to the front and </r> to the end

4. Step 3 and 4 together will replace the commas and make a XML out of the comma-seperated string.

at the end of this step, your 'HC01,HC02' will look like <r>HCC01</r><r>HC02</r> which forms an XML.

INSERT INTO @Result (ItemNumber, ItemValue)

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,

Item.value('text()[1]', 'varchar(max)') AS ItemValue

FROM @XML.nodes('//r') R(Item);

This is the real piece where the XML tags are parsed for the values between <r> and </r>

1. This - @XML.nodes('//r') - will identify the value betwent he tags (XNode is being used here)

2. R(Item) - Aliases the parsed value

3. Item.value('text()[1]' - This is where the parsed value is absorde and formed as a row

4. 'varchar(max)' - Cast the above step to VARCHAR(MAX)

At the end of this, you will have 2 rows, for HC01 and HC02

Hmmm, tommy showed the thread where people "fought" for the best parsing method.. In my function (which is above tommyh's function), i used the fastest string parser. and also mine is a inline-table valued funtion which is faster than scalar valued functions... Just try to run tommy's code and mine over a large data set and you will understand which is the fastest..

~Edit : Hyperlinked my function