question

manuu avatar image
manuu asked

using sql server string functions to extract the values

I have a column transactionID which have data like '3368/00392224/000/00' in this format. Now I have to extract data from this column in to four columns like Column name Data A 3368 B 00392224 C 000 D 00 I have to achieve this using sql server string functions to extract the values with good efficiency. Can anyone help me out. ,
inline-table-valued-functioncalculated-columnderived-column
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
I would suggest you take a look at this [article by Jeff Moden][1]. He explains how to use a tally table to split a comma delimited string. Your situation is only a little different with the delimiters being slashes, so modifying Jeff's code should be pretty easy. [1]: http://www.sqlservercentral.com/articles/Tally+Table/72993/
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reference, Grant. Actually, you don't need to change the code in that article. It'll take any single character delimiter. If you want it to be case sensitive and ignore accented characters, just add a binary collation to it. That'll speed it up a bit, as well.
0 Likes 0 ·
Well there you go then. I knew it was the right place to go.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.