?
Solved

SQL Like with regular expression

Posted on 2014-03-05
2
Medium Priority
?
569 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

771 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