Parsing a varchar(79) column

[Edgar]

Hi,

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.

[SQL]

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

Is this homework??

Denis the SQL Menace
http://sqlservercode.blogspot.com/

[Edgar]

No, it's a project for my company.

[Edgar]

Hi,

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
someday.

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)
end,
line2 =case
when @delimpos2 > 0 then substring(@txt,@delimPos2
-3,(@delimPos3 -4) - (@delimPos2 -3))
else null
end,
line3 = case
when @delimpos3 > 0 then substring(@txt,@delimPos3 -3,80 -
(@delimPos3 -2))
else null
end
go
Output will look like this:
Line1 Line2 Line3
BNR=First item KKR=Second item CNC=Third item

[--CELKO--]

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.