?
Solved

SQL Like with regular expression

Posted on 2014-03-05
2
Medium Priority
?
591 Views
Last Modified: 2014-03-06
Is it possible to create a not like regular expression so that I can filter out records base on multiple values?

So the query example is something like:

DECLARE @VALUE1 VARCHAR(80), @VALUE2 VARCHAR(80)
SET @VALUE1 = 'ABS'
SET @VALUE2 = 'NONE'

SELECT ID, DESCRIPTION
FROM ITEM_MASTER
WHERE (DESCRIPTION NOT LIKE '%'+@VALUE1+'%' OR @VALUE1 = '')
        (AND DESCRIPTION NOT LIKE '%'+@VALUE2+'%' OR @VALUE2 = '')

The above works, but issue is that users can have more than just 2 filters.  The filters can vary from 0 to 10 different filter since it's base off description.  Can something be set so that it can be just 1 parameter and a function can parse values?

Example:

DECLARE @VALUE1 VARCHAR(MAX)
SET @VALUE1 = 'ABS,NONE)

SELECT ID, DESCRIPTION
FROM ITEM_MASTER
WHERE (DESCRIPTION NOT LIKE (SELECT VALUE FROM DBO.VALUESTABLE(@VALUE1, ',')) OR @VALUE = '')

Here's the function I found online, but this only works if it's DESCRIPTION IN and does not work with LIKE.

CREATE FUNCTION [dbo].[VALUESTABLE]
(
@VALUES VARCHAR(MAX),
@SEPARATOR VARCHAR(2)
) RETURNS @RES TABLE ([VALUE] VARCHAR(MAX))
AS
BEGIN
DECLARE @VALUE VARCHAR(MAX)
DECLARE @COMMAPOS INT, @LASTPOS INT
SET @COMMAPOS = 0

SELECT @LASTPOS = @COMMAPOS, @COMMAPOS = CHARINDEX(@SEPARATOR, @VALUES, @LASTPOS+1)
WHILE @COMMAPOS>@LASTPOS
BEGIN
      SELECT @VALUE = SUBSTRING(@VALUES, @LASTPOS+1, @COMMAPOS-@LASTPOS-1)
      INSERT INTO @RES SELECT @VALUE WHERE @VALUE <> ''
      SELECT @LASTPOS = @COMMAPOS, @COMMAPOS = CHARINDEX(@SEPARATOR, @VALUES, @LASTPOS+1)
END
SELECT @VALUE = SUBSTRING(@VALUES, @LASTPOS+1, LEN(@VALUES))
INSERT INTO @RES SELECT @VALUE WHERE @VALUE <> ''
RETURN END
0
Comment
Question by:holemania
2 Comments
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 2000 total points
ID: 39907224
drop table #temp

select 'AAAA' descr into #temp
insert into #temp select 'BBBB'
insert into #temp select 'CCCC'
insert into #temp select 'ABAB'
insert into #temp select 'ACAC'
insert into #temp select 'DDDD'

declare @tester varchar(max)
set @tester = 'C, D'

--returns matches for each condition
;with CTE
as
(
select *, row_number() over (partition by descr order by item) myRow
from #temp t
join dbo.fn_txt_split(@tester, ',') s on t.Descr not like '%' + s.item + '%'
)

--give me only the matches that work for ALL conditions
select descr from cte
group by descr
having max(myRow) = (select count(*) from dbo.fn_txt_split(@tester, ','))

Open in new window



The code for FN_txt_split is:
/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 03/05/2014 13:45:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Txt_Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Txt_Split]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 03/05/2014 13:45:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(8000) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(8000)) 
as begin 
DECLARE @Item Varchar(8000) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 



GO

Open in new window

0
 

Author Closing Comment

by:holemania
ID: 39909654
Thank you.  That works great.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question