Eirikur Eiriksson (12/4/2014)

---

Quick suggestion (pseudo code)

😎

DECLARE @ParamTable varchar(100) = N''; -- include schema, error handling to deal with

DECLARE @ParamValue varchar(100) = N'';

DECLARE @ParamCaption varchar(100) = N'';

DECLARE @SearchText varchar(100) = N'';

DECLARE @CSFlag bit = 0 ;

DECLARE @params NVARCHAR(MAX) = N'@ParamValue varchar(100),@ParamCaption varchar(100),@SearchText varchar(100)';

DECLARE @dsql varchar(1000) = N'

WITH searchtext(searchtext) AS (SELECT REPLACE(@searchtext,''*'',''%''))

SELECT ParamValue = @ParamValue, ParamCaption = @ParamCaption

FROM '+ /* Your clean string function ;-) */ PATEXCLUDE(@ParamTable,'^[-;]') +N'

CROSS APPLY searchtext st

WHERE ('+cast(@CSFlag AS char(1))+N' = 0 AND 0 <> PATINDEX(searchtext,@ParamCaption))

OR ('+cast(@CSFlag AS char(1))+N' = 1 AND 0 <> PATINDEX((searchtext COLLATE Latin1_General_BIN),@ParamCaption))';

EXECUTE SP_EXECUTESQL @dsql, @params, @ParamValue,@ParamCaption,@SearchText;