How to Parse two string Columns using a Function

Question

How to Parse two string Columns using a Function

onlygoodones

SSC Eights!

Points: 870
More actions
July 18, 2013 at 9:35 am

#276123

I need to parse the sample data below as shown. Please help me in writing a TSQL Function.
Create Table Ramesh_StringParsing
(
Col1 Varchar (50),
Col2 Varchar(50)
)
Insert Into Ramesh_StringParsing (Col1, Col2)
VALUES ('A123|B3456|G546|V897|', 'Add|Delete|Insert|Update|')
Insert Into Ramesh_StringParsing (Col1, Col2)
VALUES ('C334|','Apple|')
Insert Into Ramesh_StringParsing (Col1, Col2)
VALUES ('R234|U768|X787','Ram|Sam|John')
Insert Into Ramesh_StringParsing (Col1, Col2)
VALUES ('F44|K456', 'Paint|Faint')
My Output should be:
column1column2
A123 Add
B3456Delete
G546 Insert
V897 Update
C334 Apple
R234 Ram
U768 Sam
X787 John
F44 Paint
K456 Faint

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Have you tried the 8K Splitter[/url]?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Take a look into well-known J.Moden splitter function http://www.sqlservercentral.com/articles/Tally+Table/72993/

If you cross apply to this function on each column then you can join on ItemNo function return and you will get exact output you want:

select c1.Item, c2.Item

from (select r.Col1, r.Col2, f.*

from Ramesh_StringParsing as r

cross apply dbo.DelimitedSplit8k(r.Col1, '|') f) as c1

join (select r.Col1, r.Col2, f.*

from Ramesh_StringParsing as r

cross apply dbo.DelimitedSplit8k(r.Col2, '|') f) as c2

on c2.PosNo = c1.PosNo

and c2.Col1 = c1.Col1

and c2.Col2 = c1.Col2

where c1.Item != '' or c2.Item != ''

Please note, as you didn't ptovide any indication of the keys in your sample table, my example joins on Col1 and Col2. I hope in real life you have some PK in this table - you better to join on it (you will need to list PK column(s) in sub-queries).

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

onlygoodones SSC Eights! Points: 870 More actions · Answer 3

Thanks a lot guys for quick reply.

What does PosNo Stands for in your Code below?

on c2.PosNo = c1.PosNo

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

In the original function it's returned as ItemNumber instead of PosNo

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Luis Cazares (7/18/2013)
In the original function it's returned as ItemNumber instead of PosNo

Yeah, I have slightly modified version in my play-ground. As per Luis point, just replace PosNo with ItemNumber and query should work.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

onlygoodones SSC Eights! Points: 870 More actions · Answer 6

onlygoodones

SSC Eights!

Points: 870

July 18, 2013 at 10:12 am

#1633715

Thanks a lot friend. It worked like a charm.