By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

Parsing a varchar(79) column

P: n/a
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.

May 18 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
SQL
>>Has anybody done a similar assignment? Can you share me your solution?

Is this homework??

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

May 18 '06 #2

P: n/a
No, it's a project for my company.

May 18 '06 #3

P: n/a
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

May 18 '06 #4

P: n/a
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.