how to seperate text between comma

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

General SQL Server Forums

New to SQL Server Programming

how to seperate text between comma

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-03 : 06:59:46

Dear All,

i've a string to pass as a parametre to a procedure.

like
create preocedure myproc(@EMPID VARCHAR(50),'abc,def,ghi,jkl')
...
end

i need the output like this

1 abc
2 def
3 ghi
4 jkl

.....

how can i do that?

Vinod
Even you learn 1%, Learn it with 100% confidence.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-01-03 : 07:04:48

Try this
declare @string varchar(8000)
set @string = '1,2,3,4'
select description from tbl_common_lookup_details
where ','+ @string + ',' like ',%' + description + '%,'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 07:11:42

[code]DECLARE @Var varchar(max),@Str varchar(50)
DECLARE @Temp TABLE
(
ID int IDENTITY(1,1),
Str varchar(50)
)

SELECT @Var='abc,def,ghi,jkl'

WHILE @Var IS NOT NULL
BEGIN
SELECT @Str= CASE
WHEN CHARINDEX(',',@Var)>0 THEN LEFT(@Var,CHARINDEX(',',@Var)-1)
ELSE @Var
END,
@Var=CASE
WHEN CHARINDEX(',',@Var)>0 THEN RIGHT(@Var,CHARINDEX(',',@Var)+1)
ELSE NULL
END

INSERT INTO @Temp (Str)
SELECT @Str
END

select * from @Temp[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-03 : 07:26:52

quote:
Originally posted by raky

Try this
declare @string varchar(8000)
set @string = '1,2,3,4'
select description from tbl_common_lookup_details
where ','+ @string + ',' like ',%' + description + '%,'

That should be

select description from (select '4' as description) tbl_common_lookup_details
where ','+ @string + ',' like '%,' + description + ',%'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-03 : 07:29:05

quote:
Originally posted by sunsanvin

Dear All,

i've a string to pass as a parametre to a procedure.

like
create preocedure myproc(@EMPID VARCHAR(50),'abc,def,ghi,jkl')
...
end

i need the output like this

1 abc
2 def
3 ghi
4 jkl

.....

how can i do that?

Vinod
Even you learn 1%, Learn it with 100% confidence.

For more methods rever http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 07:35:28

[code]DECLARE @Var varchar(max),@Str varchar(50)
DECLARE @Temp TABLE
(
ID int IDENTITY(1,1),
Str varchar(50)
)

SELECT @Var='abc,def,ghi,jkl'

WHILE @Var IS NOT NULL
BEGIN
SELECT @Str= CASE
WHEN CHARINDEX(',',@Var)>0 THEN LEFT(@Var,CHARINDEX(',',@Var)-1)
ELSE @Var
END
SELECT CHARINDEX(',',@Var)+1
SELECT @Var=CASE
WHEN CHARINDEX(',',@Var)>0 THEN SUBSTRING(@Var,CHARINDEX(',',@Var)+1,LEN(@Var)-CHARINDEX(',',@Var))
ELSE NULL
END

INSERT INTO @Temp ([Str]) VALUES(@Str)

END

select * from @Temp[/code]

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-03 : 07:48:25

Thank You Visakh

Vinod
Even you learn 1%, Learn it with 100% confidence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-03 : 09:27:42

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources