comma separated values usage in where clause
-
Hi Everyone
I have a table which has column name memberlist with values ('A,B,C') -> Varchar. Whenever the user enters search criteria for memberlist textfield as (A or B or C 
OR (A,B,C) the query must be such that, where clause must pull out and check whether the memberlist is A or B or C. (Is it something to deal with looping ?)Eg:
Select * from member where memberlist like 'A' or memberlist like 'B' or memberlist like 'C'.
TIA
Sathya
-
(I may not understand exactly what you are trying to do but...)
Are you trying to limit the returning rows to ANY record that contains the search criteria OR only exact matches?
If you want every record to that matches in any way:
SELECT * FROM member WHERE memberlist like '%A%'
If you want only exact matches:
SELECT * FROM member WHERE memberlist = 'A'
-
IMHO memberlists should be documented in a separate entity containing at least listID and memberID.
This way your queries will be easy and straightforward.
With the kind of list you are using in your textfield, you hide the inteligence from the db-engine, so you'll have to do with the less performant
where memberlist like '%A,%'
or memberlist like '%B,%'
or memberlist like '%C,%'
I would also advise to have the separator (comma) always in place, so you can use it when querying.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you are going to store a comma seperated list and then want to search for distinct values in the list, you will need to include a leading comma with the string. Otherwise, you will not be able to search the first value. The query above will return any memberlist entry that ends in a,b,or c.
WHERE MemberList LIKE '%,A,%' or MemberList LIKE '%,B,%' OR MemberList LIKE '%,C,%'
Brian
-
if exists
(select * from sysobjects where id = object_id('dbo.udf_10_comma_delimited_char') and xtype = 'TF')
drop function dbo.udf_10_comma_delimited_char
GO
CREATE
FUNCTION dbo.udf_10_comma_delimited_char(@list varchar(8000), @Delimiter VARCHAR(10) = ',')
/*
Original Need : By passing in a delimited set of values
The code will return a table with the items seperated
Sample Usage :
SELECT ITEM
FROM dbo.udf_10_comma_delimited_char('abc,def,ghi,jkl' , ',' )
ORDER BY ITEM DESC --or whatever WHERE, GROUP BY, HAVING, ORDER BY clause you can muster up
*/
RETURNS
@tablevalues TABLE
(item
varchar(8000))
AS
BEGIN
DECLARE @P_item varchar(255)
WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END
INSERT INTO @tablevalues
SELECT Item = @p_Item
END
RETURN
END
GO
--GRANT EXECUTE ON udf_10_comma_delimited_char TO someUser
Now you can do a
declare @v-2 varchar(16)
select @v-2 = 'A,B,C' --or read this value from the database.
select MemberList , otherField1, otherField2 from myTable
WHERE MemberList IN (
SELECT ITEM
FROM dbo.udf_10_comma_delimited_char(@v, ',' )
)
-
hi yaar
Thanx for the reply. it really worked out well........
Rgds
Sathya
-
hi yaar
Thanx for the reply. it really worked out well........
Rgds
Sathya
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply