Search commas Seprate data - 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

General SQL Server Forums

New to SQL Server Programming

Search commas Seprate data

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2010-07-07 : 09:47:01

My table have one column
verticalid Nvarchar(50)

data are :-
-------------------
Sno Verticalid
-------------------
1 13,1,3,2,18
2 9,2,16,4,15
3 2,3
4 4,8
5 15
6 11,12,18
7 3,2
8 1,2

my query is -
select * from tbl_Consultant where VerticalID in ('1,2')
My result should be

-------------------
Sno Verticalid
-------------------
1 13,1,3,2,18
2 9,2,16,4,15
3 2,3
7 3,2
8 1,2

How can i do this .
Please help me out Sir .

Yaman

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-07 : 10:20:45

This is Visakhs ParseValues Function, which you can use 'pull apart' your string

CREATE FUNCTION ParseValues
(@String varchar(8000)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val int
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

DECLARE @tbl_Consultant TABLE (Sno tinyint,VerticalID varchar(50))

INSERT INTO @tbl_Consultant
SELECT 1,'13,1,3,2,18' UNION
SELECT 2,'9,2,16,4,15' UNION
SELECT 3,'2,3' UNION
SELECT 4,'4,8' UNION
SELECT 5,'15' UNION
SELECT 6,'11,12,18' UNION
SELECT 7,'3,2' UNION
SELECT 8,'1,2'

SELECT distinct sno,verticalid
FROM @tbl_Consultant
CROSS APPLY dbo.ParseValues(verticalid,',')
WHERE val in (1,2)

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-07 : 12:03:33

You might want to look into Normalizing your database:
http://en.wikipedia.org/wiki/Database_normalization

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources