Splitting a description field into multiple rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Splitting a description field into multiple rows

Author

Topic

neostar
Starting Member

18 Posts

Posted - 2005-02-03 : 10:18:45

Sorry if this has been posted before, I do use the search function on forums, but I couldn't find a complete solution.

I basically have the following data stored in a table:

E_ITNO E_EXTDESC
568-0037 TAPE1009A KYOCERA HF800

I would like to write a query that simply returns the following to a grid

E_ITNO E_EXTDESC
568-0037 TAPE1009A
568-0037 KYOCERA
568-0037 HF800

The field already has the carriage returns in the right places I just need to pick them out. Bare in mind that the number of rows can be random, its not fixed at 3 like the example above.

Any help would be appreciated thanks

Dan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-03 : 10:41:27

Rob's article should help you out:

http://www.sqlteam.com/item.asp?ItemID=2652

same basic concept, except instead of comma's you will use carriage returns to parse your values.

- Jeff

neostar
Starting Member

18 Posts

Posted - 2005-02-03 : 10:47:26

quote:
Originally posted by jsmith8858

Rob's article should help you out:

http://www.sqlteam.com/item.asp?ItemID=2652

same basic concept, except instead of comma's you will use carriage returns to parse your values.

- Jeff

Thanks for that there is some good info in there. How do I represent a carriage return in sql though rather than the comma?

Thanks

Dan

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-03 : 10:59:56

char(13) or char(10) one is newline one is carriage return. so whichever works for you.

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-03 : 20:07:37

Actually, you need BOTH char(13) and char(10), in that order. Either one by themselves will cause strange formatting issues in Windows or DOS.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-04 : 01:50:57

What about this?

Declare @t table(E_ITNO varchar(20), E_EXTDESC varchar(100))
insert into @t values('568-0037' ,'TAPE1009A KYOCERA HF800')
select E_ITNO, parsename(replace(E_EXTDESC+' tempString',' ','.'),4)as E_EXTDESC from @t union all
select E_ITNO, parsename(replace(E_EXTDESC+' tempString',' ','.'),3)from @t union all
select E_ITNO, parsename(replace(E_EXTDESC+' tempString',' ','.'),2)from @t

Madhivanan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-04 : 09:06:51

quote:

What about this?

Declare @t table(E_ITNO varchar(20), E_EXTDESC varchar(100))
insert into @t values('568-0037' ,'TAPE1009A KYOCERA HF800')
select E_ITNO, parsename(replace(E_EXTDESC+' tempString',' ','.'),4)as E_EXTDESC from @t union all
select E_ITNO, parsename(replace(E_EXTDESC+' tempString',' ','.'),3)from @t union all
select E_ITNO, parsename(replace(E_EXTDESC+' tempString',' ','.'),2)from @t

quote:

Bare in mind that the number of rows can be random, its not fixed at 3 like the example above.

- Jeff

neostar
Starting Member

18 Posts

Posted - 2005-02-07 : 02:49:40

Managed to get this sorted with the help posted here.

Thanks very much

Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources