Solved

SQL Like with regular expression

Posted on 2014-03-05
2
550 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 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
Comment Utility
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
Comment Utility
Thank you.  That works great.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now