Parse values from delimited string

Hi. I have a string like this which will be passed into a SQL Server 2000 stored procedure,

[15438|39][21347|96][24198|23]....

I need to take the values in the above string and insert them into a temp table so they will look like this. How can a delimited string be parsed into this? Thanks.

Exam ID Branch Number 15438 39 21347 96 24198 23

Look at the use of Jens' Split function, available here.

Seems like you need to split on the brackets, ][

and then again on the pipe, |

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||The Split function returns a table. How would I call the function if it needs to split the string twice? An example is very much appreciated. Also, I'm thinking of passing the data this way to make it even simpler,

|23415^33|23451^23|....
|||

I recommand to use the XML input rather than the delimted string. You already have a structured string. Just replace the string as follow as and utilize the OPENXML; Since your schema is simple it will be simple and faster. But String manipulation will hit your performance badly(bcs you have to do split of split of split).

Sample with your input...

Code Snippet

Declare @.Data as Varchar(100);

Declare @.XML as Varchar(8000);

Set @.Data = '[15438|39][21347|96][24198|23]'

Set @.XML = Replace(Replace(Replace(@.Data,'[','<row><col1>'),'|','</col1><col2>'),']','</col2></row>')

select @.XML = '<Data>' + @.XML + '</Data>'

Declare @.iDoc as Int;

Exec sp_xml_preparedocument @.iDoc OUTPUT, @.XML

Select * From OpenXML(@.iDoc, 'Data/row', 2) With (col1 int, col2 int)

Exec sp_xml_removedocument @.iDoc

You can pass the input from your server as follow as. (Advantage: You can pass text datatype from your UI to database, but delimted values wont allow more than 8000 chars)

Code Snippet

<Root>

<row>

<Col1>15438</Col1>

<Col2>39</Col2>

</row>

<row>

<Col1>21347</Col1>

<Col2>96</Col2>

</row>

<row>

<Col1>24198</Col1>

<Col2>23</Col2>

</row>

</Root>

|||Since you can alter the input string format, I suggest that you use comma delimited, and follow Mani's suggestion about using XML.|||

Mani,

You're quite right. String manipulation is not good with T-SQL. (Unfortunately, shredding xml isn't so hot either...)

But in this case, it may be the lesser of the evils...

|||

Yes Arnie. Since the schema is simple OPENXML perform well.

In future if they want to migrate to SQL Server 2005, they can utilize the XQuery featuer. Where we can avoid the preparedocument.

|||

As a quick aside, did you verify the code you posted for the OP?

It didn't run for me...

|||Yes. All tags are got supressed. Corrected Now. Thank you. |||You should fix the client or application sending the data to send it in a different format (xml, fixed length blob) or call the SP multiple times. You can also dump the rows into a temporary table and process the rows in the SP. Doing string manipulations is slow and kludgy.