Commas in a select statement - 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)

Commas in a select statement

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-13 : 11:02:26

Is it possible to efficienlty splice a single column that contains a comma seperated list into a select insert statement?

insert into @MyTab (col1, col2, col3)
select
left(text_field, charindex(',', text_field)-1),
????
????
from A_Table

the left statement works a treat and gives me the correct entry, but col2 and col3 is getting very messing using nested charindex, left and substings and am worried on a large recordset could drag a lot?

Thnx

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-13 : 13:44:32

have found an old post of mine which dealt with this very thing, all sorted :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-14 : 01:54:27

And what is your solution?

Madhivanan

Failing to plan is Planning to fail

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-14 : 16:35:08

This works a treat :)

UPDATE @MyTab
SET @I1 = CHARINDEX(',', note + ',' ),
code1 = CASE WHEN @I1-1 > 0 THEN LTRIM(SUBSTRING(note + ',', 1, @I1-1)) ELSE '' END,
@I2 = CHARINDEX(',', note + ',' , @I1+1),
code2 = CASE WHEN @I2-@I1-1 > 0 THEN LTRIM(SUBSTRING(note + ',', @I1+1, @I2-@I1-1)) ELSE '' END,
@I3 = CHARINDEX(',', note + ',' , @I2+1),
code3 = CASE WHEN @I3-@I2-1 > 0 THEN LTRIM(SUBSTRING(note + ',', @I2+1, @I3-@I2-1)) ELSE '' END,
@I4 = CHARINDEX(',', note + ',', @I3+1),
code4 = CASE WHEN @I4-@I3-1 > 0 THEN LTRIM(SUBSTRING(note + ',', @I3+1, @I4-@I3-1)) ELSE '' END
FROM @MyTab

The case statement caters for non entries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources