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' , ',' ) 
*/

Rate

Share

Share

Rate