Text To Columns - SQL Server Forums

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)

Text To Columns

Author

Topic

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-09-07 : 12:10:51

This question may have already been asked but I could not find it. I have a column that was provided to me, that has multiple id's in it, each separated by a space. Is there a function or series of queries that I can perform to put each individual id in a separate row and/or column?

Thanks!

*************************
Got some code from Rob. Can anyone help?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 12:15:30

this may help you:

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

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

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-09-07 : 14:16:43

What if the items are not separated by a comma (CSV)? Can this be done with just spaces?

*************************
Got some code from Rob. Can anyone help?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 15:00:11

yes, that's just a delimiter. and space is a delimiter too

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

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-09-07 : 15:46:43

Thank you so much for your assistance.

Couple of questions since I have been trying to use this solution for my situation. I am working with numeric information.

INSERT INTO EBP_DACS
SELECT BAN, NullIf(SubString(',' + DAC_NO + ',' , IDS , CharIndex(',' , ',' + DAC_NO + ',' , IDS) - IDS) , '') AS DAC
FROM ndw_research.dbo.Tally, ndw_research.dbo.EPB_ACCUM_CHARGE
WHERE ID <= Len(',' + DAC_NO + ',') AND SubString(',' + DAC_NO + ',' , IDS - 1, 1) = ','

Can you use SubString on numerics?
Should I change the CharIndex to something else?

Any idea how to create the "tally" table? I know how to create a table, but can you help me to create the loop statement to add the sequential numbers? I have tried the following, but the rows are not updating.

declare @intCounter int
set @intCounter = 0
update tally
SET ids = @intCounter + 1
from tally
where ids<7000

*************************
Got some code from Rob. Can anyone help?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 16:39:56

substring on numerics no. You must convert it to varchar
select substring(cast(DAC_NO as varchar(10)), 2, 2)
that should help you with other casts.

these both work on the same principle. just to show u how it can be done both ways.

tally table:


select t1.n + t2.n + t3.n + t4.n as n
from (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5  
	union all select 6 union all select 7 union all select 8 union all select 9  union all select 0) t1,
(select 10 as n union all select 20 union all select 30 union all select 40 union all select 50  
	union all select 60 union all select 70 union all select 80 union all select 90 union all select 0) t2,
(select 100 as n union all select 200 union all select 300 union all select 400 union all select 500  
	union all select 600 union all select 700 union all select 800 union all select 900  union all select 0) t3,
(select 1000 as n union all select 2000 union all select 3000 union all select 4000 union all select 5000  
	union all select 6000 union all select 7000 union all select 8000 union all select 9000  union all select 0) t4
order by n


-- help table variable with 0,1,2,3,4,5,6,7,8,9
declare @tally table( n int)
while ( select count(*) from @tally ) < 10
	insert @tally select count(*) from @tally

select 	t1.n + 10 * t2.n + 100 * t3.n + 1000 * t4.n as n 
from 	@tally t1 cross join @tally t2 cross join @tally t3 cross join @tally t4  
order by n

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

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-09-07 : 16:50:47

quote:
Originally posted by spirit1

select substring(cast(DAC_NO as varchar(10)), 2, 2)

If it is a straight CAST, shouldn't it be CAST(DAC_NO as varchar(10))? What is the ,2,2) for?

*************************
Got some code from Rob. Can anyone help?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 16:58:14

that's for substring part

. it means to return 2 char from 2 charindex.

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

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-07 : 17:10:33

Or another way to create a tally table that I can remember to type (and have the patience to type)

SELECT top 100000 n = identity(int,1,1)
INTO tally
from sysobjects s1, sysobjects s2, sysobjects s3

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 17:16:25

way cool! a bit slow, though.

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

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-09-07 : 17:48:46

Thank you everyone for your assistance, esp spirit1. Was able to do what I needed to. Again...Thanks!

*************************
Got some code from Rob. Can anyone help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources