Posted by Edgar on 05/18/06 22:04

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

• Next in forum: Help to optimize query
• Prev in forum: Re: Parsing a varchar(79) column
• Thread view: Re: Parsing a varchar(79) column

[Reply to this message]