Is there a 'split' function in SQL?

[redlam]

I am using a MSSQL7 database which has a table with 2 fields, one defined as int and the other defined as varchar(8000).&nbsp;&nbsp;The values in this field are pipe delimited and look like this:<br><br>fssportid fsdraftlist&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>--------- ------------------------------ <br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3898¦2731¦2734¦3867¦4016¦3013¦...<br><br>Does anyone know any easy way using SQL to convert this recordset into a normalized table that would look like this:<br><br>fssportid fsdraftlist&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>--------- ------------------------------ <br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3898<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2731<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2734<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3867<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4016<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3013

 

[Arak]

declare @a int<br>declare @b char(4)<br>select @a = 1<br>While @a &lt; select len(list) from sourcetable<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;begin<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select @b = substring(list,@a,4) from sourcetable<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;insert into targettable (portid, @b)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select @a = @a + 5<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end<br>

 

[redlam]

Thanks, that seems to work when the values between pipes are 4 characters.&nbsp;&nbsp;I probably should have mentioned that these values are of varying length.&nbsp;&nbsp;For example the data could look like this:<br><br>fssportid fsdraftlist&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>--------- ------------------------------ <br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3898¦31¦4¦386¦4016¦3013¦...<br><br>I need to turn it into this:<br><br>fssportid fsdraftlist&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>--------- ------------------------------ <br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3898<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;386<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4016<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3013 <br><br>

 

[THoey]

Use the same idea that arak mentioned, except instead of knowing that the fields are four characters long, use the INSTR() function to look for the next delimiter.&nbsp;&nbsp;&nbsp;Once you know where the next delimiter is, you can use the MID() function to strip pieces out.<br><br>If you need further info, email me... <p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>

 

[redlam]

Thanks for your help everyone!&nbsp;&nbsp;While the suggestions didn't 'exactly' work for me since I am only using SQL and there is no INSTR or MID function available - they certainly steered me in the right direction.&nbsp;&nbsp;I was able to parse the delimited field by using a combination of both suggestions but instead of INSTR and MID, I had to use the CHARINDEX function to determine the location of the next delimiter.&nbsp;&nbsp;Thank you, thank you, thank you.&nbsp;&nbsp;I never would've figured that out on my own.