sql search from csv string

Question

im doing a search page where i have to search multiple fields with a single textbox. so i will get the search text as a CSV string in my stored procedure

My table is as below

ID  Name          age   
5   bob           23    
6   bod.harry     34    
7   charles       44    

i need a sql query something like this

declare @searchtext='bob,harry,charley'
select * from employee where  name like (@searchtext) 

this query should return both this records (id 5 and 6)

Answer 1

You can use this way in Stored Procedure,

declare @searchtext varchar(1000)

set searchtext ='bob,harry,charley'

declare @filter varchar(2000)

set @filter = '(name LIKE ''%' + replace('bob,harry,charley',',','%'' OR name LIKE ''%') + '%'')'

exec
('
    select *
    from mytab
    where ' + @filter + '
'
)

Answer 2

Use (or adapt) this splitting function:

ALTER FUNCTION [dbo].CsvToList(@SplitOn  char(1), @List varchar(8000))
RETURNS TABLE
AS
RETURN 
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY number) AS RowNumber
            ,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue
        FROM (
                 SELECT @SplitOn + @List + @SplitOn AS ListValue
             ) AS InnerQuery
            INNER JOIN master.dbo.spt_values n ON n.Number < LEN(InnerQuery.ListValue)
        WHERE SUBSTRING(ListValue, number, 1) = @SplitOn
        AND n.type = 'P'
);
GO 

usage

declare @searchtext='bob,harry,charley'
select DISTINCT * from employee e
JOIN dbo.csvToList(',', @searchtext) f
  ON f.ListValue = e.name

Answer 3

You'll need to break @searchtext into multiple strings, one for each name. It's doable in TSQL but may be easier in your application code. You can then compare those with your Name field.

Answer 4

If I'm not mistaken Sql-Server doesn't support Regex. You can use table valued parameters. If you are using Entity framework the you could do so.

var dc = new MyContext();
var result = dc.employees.Where(x => new [] { "bob", "harry", "charley" }.Contains(x.name));

and finally you might construct the following

select * from employee where  name in (@Param1, @Param2, @Param3, @Param4)

EDIT

I highly discourage you to use CSV because of the performance drop (you have to parse your csv) and possibility of errors (consider this csv Foo,Bar,"Foo with, comma","comma, "" and quote")

P.S. If you use table valued parameter when you assign the value use DataTable as source.

Answer 5

The above version of [dbo].CsvToList does not work with long input string with a lot of separators. Table spt_values where type='P' has limited number of records. In my case the function returned 16 rows instead of 66. Some advise to create your own table with numbers. I used a different version of this function I copied from other place:

CREATE FUNCTION [dbo].[fngCsvToList](@SplitOn char(1), @List varchar(8000))
RETURNS @Result TABLE (ListValue varchar(100))
AS
BEGIN 
  DECLARE @str VARCHAR(20) 
  DECLARE @ind Int 
  IF(@List is not null) 
  BEGIN 
    SET @List = REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@List)), CHAR(10), ''),    CHAR(13), ''), CHAR(9), '')

        SET @ind = CharIndex(@SplitOn, @List) 
        WHILE @ind > 0 
        BEGIN 
              SET @str = SUBSTRING(@List, 1, @ind-1) 
              SET @List = SUBSTRING(@List, @ind+1, LEN(@List)-@ind) 
              INSERT INTO @Result values (LTRIM(RTRIM(@str)))

              SET @ind = CharIndex(',',@List) 
        END 

   SET @str = @List
        INSERT INTO @Result values (LTRIM(RTRIM(@str) ))
  END 
  RETURN 
END