Solved

SQL Like with regular expression

Posted on 2014-03-05
2
559 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 40

Accepted Solution

by:
Kyle Abrahams earned 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 27
Sql server insert 13 28
TSQL query to generate xml 4 32
SQL Error - Query 6 24
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.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 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