Delimited String Parsing and Evaluation Functions
Here's a quick and dirty function for parsing strings based on a delimiter (As in a varchar field with comma delimited text). I'm hoping for feedback on a more efficient means of doing this.
Also,
4 functions for comparing two delimited lists of strings. Many may ask why these are needed, but that's a very long explanation (I'd have to be not lazy and write an entire article). I'm hoping someone gets some use out of these, finds better ways to do it or has better suggestions on how...
DROP FUNCTION string_field
GO
CREATE FUNCTION string_field
( @string VARCHAR(4000) = NULL
,@delimiter VARCHAR(20)
,@position INT
) RETURNS VARCHAR(255)
AS
BEGIN
---NOTE: modified because prior version didn't handle space as delimiter
DECLARE @result VARCHAR(255)
,@work VARCHAR(4000)
,@pattern VARCHAR(255)
,@i INT
,@j INT
,@ld INT
SELECT @result = ""
,@ld = LEN( REPLACE( @delimiter, ' ', 'X' ) ) --Incase delimiter is a space LEN will evaluate to zero
--and we don't want that.
IF @position > 0 BEGIN
SELECT @pattern = "%" + @delimiter + "%"
,@work = @string
,@j = 0 --init
WHILE ( @j < @position ) BEGIN
SELECT @i = PATINDEX( @pattern, @work )
,@j = @j + 1
IF @i > 0 BEGIN
SELECT @result = SUBSTRING( @work, 1, @i - 1 )
,@work = SUBSTRING( @work, @i + @ld, 4000 )
END ELSE BEGIN
IF @j = @position
SELECT @result = @work
ELSE
SELECT @result = ""
,@j = @position
END
END
END
RETURN (@result)
END
GO
------------------------------------------------------------
--************************************************************
--************************************************************
------------------------------------------------------------
DROP FUNCTION in_list_or
GO
CREATE FUNCTION in_list_or
( @look_for_list VARCHAR(1000)
,@in_list VARCHAR(1000)
,@delim VARCHAR(1)
)
RETURNS VARCHAR(1)
AS
BEGIN
-----------------------------------------------------------------------------------------------
-- GIVEN two similarly delimited string lists of values
-- See if any of the values in the @look_for_list exists in the @in_list.
-- (@delim is the delimiter --usually comma-- that separates the items in each list)
-- Function returns Y or N
--
-- EXAMPLE dbo.in_list_or( 'a,b,c', 'd,e,f,g,h,i,j,k' , ',' ) will = 'N'
-- EXAMPLE dbo.in_list_or( 'a,b,c', 'd,e,f,g,h,b,j,k' , ',' ) will = 'Y'
-----------------------------------------------------------------------------------------------
DECLARE @result VARCHAR(1)
,@loop_limit1 INT
,@loop_limit2 INT
, @i INT
, @j INT
,@lstring VARCHAR(1000)
,@found_flag CHAR(1)
SELECT @loop_limit1 = LEN( @look_for_list ) - LEN ( REPLACE( @look_for_list, @delim, '' ) ) + 1
,@loop_limit2 = LEN( @in_list ) - LEN ( REPLACE( @in_list, @delim, '' ) ) + 1
,@i = 1
,@found_flag = 'N'
WHILE ( @i <= @loop_limit1 ) BEGIN
SELECT @lString= dbo.string_field( @look_for_list, @delim, @i )
,@j = 1
, @i = @i + 1
WHILE ( @j <= @loop_limit2 ) BEGIN
IF @lString= dbo.string_field( @in_list, @delim, @j )
SELECT @found_flag = 'Y'
,@j = @loop_limit2 + 1
ELSE
SELECT @j = @j + 1
END
IF @found_flag = 'Y' SELECT @i = @loop_limit1 + 1
END
RETURN ISNULL( @found_flag, '' )
END
GO
/*
SELECT dbo.in_list_or ( "a,b,c,", "d,e,f", ',' )
SELECT dbo.in_list_or ( "a,b,c,e", "d,e,f", ',' )
SELECT dbo.in_list_or ( "a,b,c,e", "d,e,f", ',' )
select *
from cwk_horse_classes_v
where horse_id = 524306
AND
dbo.in_list_or( '33,54,22,20', disc_ids, ',' ) = 'Y'
select * from cwk_horse_classes_v
where horse_id = 524306
*/
------------------------------------------------------------
--************************************************************
--************************************************************
------------------------------------------------------------
DROP FUNCTION in_list_and
GO
CREATE FUNCTION in_list_and
( @look_for_list VARCHAR(1000)
,@in_list VARCHAR(1000)
,@delim VARCHAR(1)
)
RETURNS VARCHAR(1)
AS
BEGIN
-----------------------------------------------------------------------------------------------
-- GIVEN two similarly delimited string lists of values
-- Make sure that each and every value in the @look_for_list exists in the
-- @in_list.
-- (@delim is the delimiter --usually comma-- that separates the items in each list)
-- function returns Y or N
--
-- EXAMPLE dbo.in_list_and( 'a,b,c', 'd,e,f,g,h,i,j,j' , ',' ) will = 'N'
-- EXAMPLE dbo.in_list_and( 'a,b,c', 'c,e,a,g,h,b,j,j' , ',' ) will = 'Y'
-----------------------------------------------------------------------------------------------
DECLARE @result VARCHAR(1)
,@loop_limit1 INT
,@loop_limit2 INT
, @i INT
, @j INT
,@lstring VARCHAR(1000)
,@found_flag CHAR(1)
SELECT @loop_limit1 = LEN( @look_for_list ) - LEN ( REPLACE( @look_for_list, @delim, '' ) ) + 1
,@loop_limit2 = LEN( @in_list ) - LEN ( REPLACE( @in_list, @delim, '' ) ) + 1
,@i = 1
,@found_flag = 'Y'
WHILE ( @i <= @loop_limit1 ) BEGIN
SELECT @lString= dbo.string_field( @look_for_list, @delim, @i )
,@j = 1
,@i = @i + 1
,@found_flag = 'N'
WHILE ( @j <= @loop_limit2 ) BEGIN
IF @lString= dbo.string_field( @in_list, @delim, @j )
SELECT @found_flag = 'Y'
,@j = @loop_limit2 + 1
ELSE
SELECT @j = @j + 1
END
IF @found_flag = 'N' SELECT @i = @loop_limit1 + 1
END --loop thru lookforlist
RETURN ISNULL( @found_flag, 'N' )
END
GO
/*
select dbo.in_list_and( 'a,b,c', 'd,e,f,g,h,i,j,j' , ',' )
select dbo.in_list_and( 'a,b,c,', 'c,e,a,g,h,b,,j,j' , ',' )
select dbo.in_list_and( 'a,b,c,w', 'c,e,a,g,h,b,j,j' , ',' )
*/------------------------------------------------------------
--************************************************************
--************************************************************
------------------------------------------------------------
DROP FUNCTION in_list_like_or
GO
CREATE FUNCTION in_list_like_or
( @look_for_list VARCHAR(1000)
,@in_list VARCHAR(1000)
,@delim VARCHAR(1)
)
RETURNS VARCHAR(1)
AS
BEGIN
-----------------------------------------------------------------------------------------------
-- GIVEN two similarly delimited string lists of values
-- See if any of the values in the @look_for_list IS LIKE any value in the @in_list.
-- (@delim is the delimiter --usually comma-- that separates the items in each list)
-- (Values in the look_for_list use the syntax of the like comparitors)
-- Function returns Y or N
--
-- EXAMPLE value of only a,b,or c; OR ending with 'test', OR containing a Q:
-- dbo.in_list_like_or( '[a-c],%test,%Q%', 'brown, escalator, melon, splunge' , ',' ) will = 'N'
-- dbo.in_list_like_or( '[a-c],%test,%Q%', 'brown, escalator,b,melon, splunge' , ',' ) will = 'Y'
-- dbo.in_list_like_or( '[a-c],%test,%Q%', 'brown, escalatortest,melon, splunge' , ',' ) will = 'Y'
-- dbo.in_list_like_or( '[a-c],%test,%Q%', 'brown, escalator,meloQn, splunge' , ',' ) will = 'Y'
-----------------------------------------------------------------------------------------------
DECLARE @result VARCHAR(1)
,@loop_limit1 INT
,@loop_limit2 INT
, @i INT
, @j INT
,@lstring VARCHAR(1000)
,@found_flag CHAR(1)
SELECT @loop_limit1 = LEN( @look_for_list ) - LEN ( REPLACE( @look_for_list, @delim, '' ) ) + 1
,@loop_limit2 = LEN( @in_list ) - LEN ( REPLACE( @in_list, @delim, '' ) ) + 1
,@i = 1
,@found_flag = 'N'
WHILE ( @i <= @loop_limit1 ) BEGIN
SELECT @lString= dbo.string_field( @look_for_list, @delim, @i )
,@j = 1
, @i = @i + 1
WHILE ( @j <= @loop_limit2 ) BEGIN
IF dbo.string_field( @in_list, @delim, @j ) LIKE @lString
SELECT @found_flag = 'Y'
,@j = @loop_limit2 + 1
ELSE
SELECT @j = @j + 1
END
IF @found_flag = 'Y' SELECT @i = @loop_limit1 + 1
END
RETURN ISNULL( @found_flag, '' )
END
GO
/*
select dbo.in_list_like_or( '[a-c],%test,%Q%', 'brown, escalator, melon, splunge' , ',' )
select dbo.in_list_like_or( '[a-c],%test,%Q%', 'brown, escalator,b,melon, splunge' , ',' )
select dbo.in_list_like_or( '[a-c],%test,%Q%', 'brown, escalatortest,melon, splunge' , ',' )
select dbo.in_list_like_or( '[a-c],%test,%Q%', 'brown, escalator,meloQn, splunge' , ',' )
*/
------------------------------------------------------------
--************************************************************
--************************************************************
------------------------------------------------------------
DROP FUNCTION in_list_like_and
GO
CREATE FUNCTION in_list_like_and
( @look_for_list VARCHAR(1000)
,@in_list VARCHAR(1000)
,@delim VARCHAR(1)
)
RETURNS VARCHAR(1)
AS
BEGIN
-----------------------------------------------------------------------------------------------
-- GIVEN two similarly delimited string lists of values
-- Make sure all of the values in the @look_for_list are LIKE any value in the @in_list.
-- (@delim is the delimiter --usually comma-- that separates the items in each list)
-- (Values in the look_for_list use the syntax of the like comparitors)
-- Function returns Y or N
--
-- EXAMPLE value of only a,b,or c; AND ending with 'test', AND containing a Q:
-- dbo.in_list_like_and( '[a-c],%test,%Q%', 'brown, escalator, melon, splunge' , ',' ) will = 'N'
-- dbo.in_list_like_and( '[a-c],%test,%Q%', 'brown, escalatortest,b,meloQn, splunge' , ',' ) will = 'Y'
-----------------------------------------------------------------------------------------------
DECLARE @result VARCHAR(1)
,@loop_limit1 INT
,@loop_limit2 INT
, @i INT
, @j INT
,@lstring VARCHAR(1000)
,@found_flag CHAR(1)
SELECT @loop_limit1 = LEN( @look_for_list ) - LEN ( REPLACE( @look_for_list, @delim, '' ) ) + 1
,@loop_limit2 = LEN( @in_list ) - LEN ( REPLACE( @in_list, @delim, '' ) ) + 1
,@i = 1
,@found_flag = 'N'
WHILE ( @i <= @loop_limit1 ) BEGIN
SELECT @lString= dbo.string_field( @look_for_list, @delim, @i )
,@j = 1
, @i = @i + 1
,@found_flag = 'N'
WHILE ( @j <= @loop_limit2 ) BEGIN
IF dbo.string_field( @in_list, @delim, @j ) LIKE @lstring
SELECT @found_flag = 'Y'
,@j = @loop_limit2 + 1
ELSE
SELECT @j = @j + 1
END
IF @found_flag = 'N' SELECT @i = @loop_limit1 + 1
END --loop thru lookforlist
RETURN ISNULL( @found_flag, 'N' )
END
GO
/*
select dbo.in_list_like_and( '[a-c],%test,%Q%', 'brown, escalator, melon, splunge' , ',' )
select dbo.in_list_like_and( '[a-c],%test,%Q%,', 'brown, escalatortest,b,meloQn, splunge' , ',' )
*/ 
