holemania
asked on
SQL Like with regular expression
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER