471,595 Members | 1,623 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,595 software developers and data experts.

Parsing a varchar(79) column


I have a varchar column with values like:

000 BNP=Item one HOP=Item two LLT=Item three
001 LLT=Item one
002 BNP=Item one LLT=Item two

I want to parse this rows into a table similar to the following output:

000 BNP=Item one
000 HOP=Item two
000 LLT=ItemThree
001 LLT=Item one
002 BNP=Item one
002 LLT=Item two

Has anybody done a similar assignment? Can you share me your solution?

Thank you much!

Edgar J.

May 18 '06 #1
4 1155
>>Has anybody done a similar assignment? Can you share me your solution?

Is this homework??

Denis the SQL Menace

May 18 '06 #2
No, it's a project for my company.

May 18 '06 #3

Resolved on my own with a slight twist on the output.

Here is the code, just in case you may have a similar user request

declare @txt varchar(80)
declare @delimchar nchar(1)
set @delimchar = '='
declare @delimPos1 int, @delimPos2 int,@delimPos3 int
set @txt = 'BNR=First item KKR=Second item CNC=Third item '
set @delimPos1 = charindex(@delimChar,@txt,1)
set @delimPos2 = charindex(@delimChar,@txt,@delimPos1 + 1)
set @delimPos3 = charindex(@delimChar,@txt,@delimPos2 + 1)

select line1=case
when @delimpos1 = 4 then substring(@txt,1,@delimPos2 -4)
else substring(@txt,1,80)
line2 =case
when @delimpos2 > 0 then substring(@txt,@delimPos2
-3,(@delimPos3 -4) - (@delimPos2 -3))
else null
line3 = case
when @delimpos3 > 0 then substring(@txt,@delimPos3 -3,80 -
(@delimPos3 -2))
else null
Output will look like this:
Line1 Line2 Line3
BNR=First item KKR=Second item CNC=Third item

May 18 '06 #4
Google some old postings here about spliting comma separated lists. I
have a signle query version out there that uses a sequence table and no
proceudral code.

However, any typo will screw up things and you still need to go back
and fix the real problem -- lack of proper column constraints! Mop the
floor and fix the leak! Do not let this crap in your database again.

May 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Bill | last post: by
2 posts views Thread by Alexandre H. Guerra | last post: by
5 posts views Thread by John Sidney-Woollett | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.