Retrieve Data Using Comma Separated List

ASPnoob

Hi all, Suppose I have a database table named JobsTable and among the fields in the table are JobDescriptions, and JobTitle. How would I write my Where clause if users can get all the JobTitle where the JobDescriptions field contains any word in the comma separated list they entered? I tried the Like keyword but it is not doing what I want. Thanks in advance for your help.

Mycroft Holmes

I have a function (every dev does) which splits a string and returns a table with ID and Item, you could then join the split with your data table

Select *
From Table T
inner join fn_Split(@List,',') l on L.item = T.FieldName

ALTER FUNCTION [dbo].[fn_Split]
(@List varchar(8000), @Delimiter char(1))

RETURNS @Results table
(Item varchar(8000),ID int Identity(1,1))

AS

begin
declare @IndexStart int
declare @IndexEnd int
declare @Length int
declare @Word varchar(8000)

set @IndexStart = 1
set @IndexEnd = 0

set @Length = len(@List)
If @Delimiter = '' Set @Delimiter = ','

--Get rid of any tabs or returns
Set @List = Replace(@List,char(9),'')
Set @List = Replace(@List,char(10),'')
Set @List = Replace(@List,char(13),'')

while @IndexStart <= @Length
begin
set @IndexEnd = charindex(@Delimiter, @List, @IndexStart)

If @Delimiter = char(32) 
	set @IndexEnd = charindex(Space(1), @List, @IndexStart)

if @IndexEnd = 0
	set @IndexEnd = @Length + 1

set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
set @IndexStart = @IndexEnd + 1

INSERT INTO @Results(Item)
SELECT @Word

end

return
end

Never underestimate the power of human stupidity RAH

Shweta N Mishra

You can also convert comma separated list to rows(column to rowConverting comma separated data in a column to rows for selection[^]) and then you can run the like query against it.

Mycroft Holmes

Yeah I probably snaffled that code from someone in the 90s and have not looked at it since :-O , I'm certain there is a better way of splitting the items.

Never underestimate the power of human stupidity RAH