using sql server string functions to extract the values

Question

question

manuu asked Jul 23, '13

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-function derived-column calculated-column

10 |1200

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

Answer 1 · 2013-07-23T09:19:45Z

Grant Fritchey answered Jul 23, '13

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

10 |1200

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

Jeff Moden commented · Jul 28, 2013 at 05:16 AM

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 ·

Grant Fritchey ♦♦ commented · Jul 29, 2013 at 10:47 AM

Well there you go then. I knew it was the right place to go.

0 ·

question