SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 UDFs AT, RAT, OCCURS, OCCURS2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/17/2005 :  22:42:38  Show Profile  Visit Igor2004's Homepage  Reply with Quote


-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
-- AT() User-Defined Function 
-- Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character.
-- AT(@cSearchExpression, @cExpressionSearched [, @nOccurrence]) Return Values smallint 
-- Parameters
-- @cSearchExpression nvarchar(4000) Specifies the character expression that AT( ) searches for in @cExpressionSearched. 
-- @cExpressionSearched nvarchar(4000) Specifies the character expression @cSearchExpression searches for. 
-- @nOccurrence smallint Specifies which occurrence (first, second, third, and so on) of @cSearchExpression is searched for in @cExpressionSearched. By default, AT() searches for the first occurrence of @cSearchExpression (@nOccurrence = 1). Including @nOccurrence lets you search for additional occurrences of @cSearchExpression in @cExpressionSearched. AT( ) returns 0 if @nOccurrence is greater than the number of times @cSearchExpression occurs in @cExpressionSearched. 
-- Remarks
-- AT() searches the second character expression for the first occurrence of the first character expression. It then returns an integer indicating the position of the first character in the character expression found. If the character expression is not found, AT() returns 0. The search performed by AT() is case-sensitive.
-- AT is nearly similar to a function Oracle PL/SQL INSTR
-- Example
-- declare @gcString nvarchar(4000), @gcFindString nvarchar(4000)
-- select @gcString = 'Now is the time for all good men', @gcFindString = 'is the'
-- select dbo.AT(@gcFindString, @gcString, default)  -- Displays 5
-- set @gcFindString = 'IS'
-- select dbo.AT(@gcFindString, @gcString, default)  -- Displays 0, case-sensitive
-- See Also RAT()  User-Defined Function 
-- UDF the name and functionality of which correspond  to the same built-in function of Visual FoxPro
CREATE function AT  (@cSearchExpression nvarchar(4000), @cExpressionSearched  nvarchar(4000), @nOccurrence  smallint = 1 )
returns smallint
as
    begin
      if @nOccurrence > 0
         begin
            declare @i smallint,  @StartingPosition  smallint
            select  @i = 1, @StartingPosition  = dbo.CHARINDEX_BIN(@cSearchExpression, @cExpressionSearched, 1)
            while  @StartingPosition <> 0 and @nOccurrence > @i
               select  @i = @i + 1, @StartingPosition  = dbo.CHARINDEX_BIN(@cSearchExpression, @cExpressionSearched,  @StartingPosition+1 )
         end
      else
         set @StartingPosition =  NULL

     return @StartingPosition
    end
GO

-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca 
-- RAT( ) User-Defined Function
-- Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
-- RAT(@cSearchExpression, @cExpressionSearched [, @nOccurrence])
-- Return Values smallint 
-- Parameters
-- @cSearchExpression nvarchar(4000) Specifies the character expression that RAT( ) looks for in @cExpressionSearched. 
-- @cExpressionSearched nvarchar(4000) Specifies the character expression that RAT() searches. 
-- @nOccurrence smallint Specifies which occurrence, starting from the right and moving left, of @cSearchExpression RAT() searches for in @cExpressionSearched. By default, RAT() searches for the last occurrence of @cSearchExpression (@nOccurrence = 1). If @nOccurrence is 2, RAT() searches for the next to last occurrence, and so on. 
-- Remarks
-- RAT(), the reverse of the AT() function, searches the character expression in @cExpressionSearched starting from the right and moving left, looking for the last occurrence of the string specified in @cSearchExpression.
-- RAT() returns an integer indicating the position of the first character in @cSearchExpression in @cExpressionSearched. RAT() returns 0 if @cSearchExpression is not found in @cExpressionSearched, or if @nOccurrence is greater than the number of times @cSearchExpression occurs in @cExpressionSearched.
-- The search performed by RAT() is case-sensitive.
-- Example
-- declare @gcString nvarchar(4000), @gcFindString nvarchar(4000)
-- select @gcString = 'abracadabra', @gcFindString = 'a' 
-- select dbo.RAT(@gcFindString , @gcString, default)  -- Displays 11
-- select dbo.RAT(@gcFindString , @gcString , 3)       -- Displays 6
-- See Also AT()  User-Defined Function 
-- UDF the name and functionality of which correspond  to the same built-in function of Visual FoxPro   
CREATE function RAT  (@cSearchExpression nvarchar(4000), @cExpressionSearched  nvarchar(4000), @nOccurrence  smallint = 1 )
returns smallint
as
    begin
      if @nOccurrence > 0
         begin
            declare @i smallint, @length smallint, @StartingPosition  smallint
            select  @i = 1, @length  = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
            select  @StartingPosition  = dbo.RCHARINDEX(@cSearchExpression, @cExpressionSearched, 1)
            while @StartingPosition <> 0 and @nOccurrence > @i
               select  @i = @i + 1, @StartingPosition  = dbo.RCHARINDEX(@cSearchExpression, @cExpressionSearched,  @length - @StartingPosition + 2 )
         end
      if @StartingPosition <> 0
         select @StartingPosition = @StartingPosition + 1  - datalength(@cSearchExpression)/(case SQL_VARIANT_PROPERTY(@cSearchExpression,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
      else
         set @StartingPosition =  NULL

     return @StartingPosition
    end
GO

-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
 -- OCCURS() User-Defined Function
 -- Returns the number of times a character expression occurs within another character expression (include overlaps).
 -- OCCURS(@cSearchExpression, @cExpressionSearched)
 -- Return Values smallint 
 -- Parameters
 -- @cSearchExpression nvarchar(4000) Specifies a character expression that OCCURS() searches for within @cExpressionSearched. 
 -- @cExpressionSearched nvarchar(4000) Specifies the character expression OCCURS() searches for @cSearchExpression. 
 -- Remarks
 -- OCCURS() returns 0 (zero) if @cSearchExpression is not found within @cExpressionSearched.
 -- Example
 -- declare @gcString nvarchar(4000)
 -- select  @gcString = 'abracadabra'
 -- select dbo.OCCURS('a', @gcString )  -- Displays 5
 -- select dbo.OCCURS('b', @gcString )  -- Displays 2
 -- select dbo.OCCURS('c', @gcString )  -- Displays 1
 -- select dbo.OCCURS('e', @gcString )  -- Displays 0
 -- Attention, include overlaps !!!
 -- select dbo.OCCURS('ABCA', 'ABCABCABCA') -- display 3
 -- 1 occurrence of substring 'ABCA  .. BCABCA' 
 -- 2 occurrence of substring 'ABC...ABCA...BCA' 
 -- 3 occurrence of substring 'ABCABC...ABCA' 
 -- See Also AT(), RAT()  
 -- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
 -- (but function OCCURS of Visual FoxPro counts the 'occurs' exclude overlaps !)
CREATE function OCCURS  (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000))
returns smallint
as
    begin
      declare @start_location smallint,  @occurs  smallint
      select  @start_location = dbo.CHARINDEX_BIN(@cSearchExpression, @cExpressionSearched, 1),   @occurs = 0

     while @start_location > 0
          select  @occurs = @occurs + 1,  @start_location  = dbo.CHARINDEX_BIN(@cSearchExpression, @cExpressionSearched, @start_location+1)
    
     return  @occurs
    end
GO

 -- Author: Stephen Dobson, Toronto, EMail: sdobson@acc.org   
 -- OCCURS2() User-Defined Function
 -- Returns the number of times a character expression occurs  within another character expression ( excluding overlaps).
 -- OCCURS2(@cSearchExpression, @cExpressionSearched)
 -- Return Values smallint 
 -- Parameters
 -- @cSearchExpression nvarchar(4000) Specifies a character expression that OCCURS2() searches for within @cExpressionSearched. 
 -- @cExpressionSearched nvarchar(4000) Specifies the character expression OCCURS2() searches for @cSearchExpression. 
 -- Remarks
 -- OCCURS2() returns 0 (zero) if @cSearchExpression is not found within @cExpressionSearched.
 -- Example
 -- declare @gcString nvarchar(4000)
 -- select  @gcString = 'abracadabra'
 -- select dbo.OCCURS2('a', @gcString )  -- Displays 5
 -- Attention !!!
 -- This function counts the 'occurs' exclude overlaps !
 -- select dbo.OCCURS2('ABCA', 'ABCABCABCA') -- display 2
 -- 1 occurrence of substring 'ABCA  .. BCABCA' 
 -- 2 occurrence of substring 'ABCABC... ABCA' 
 -- UDF the functionality of which correspond to the built-in function OCCURS of Visual FoxPro
 -- See Also OCCURS()  
CREATE function OCCURS2  (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000))
returns smallint
as
    begin
         return
           case  
              when datalength(@cSearchExpression) > 0
              then   ( datalength(@cExpressionSearched) 
                   - datalength(replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN, 
                                                   cast(@cSearchExpression  as nvarchar(4000)) COLLATE Latin1_General_BIN,  '')))  
                  / datalength(@cSearchExpression)
             else 0 
          end
    end
GO

-- Is similar to the built-in function Transact-SQL charindex but the search of which is on the right
-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
CREATE function RCHARINDEX  (@expression1 nvarchar(4000), @expression2  nvarchar(4000), @start_location  smallint = 1 )
returns nvarchar(4000)
as
    begin
       declare @StartingPosition  smallint
       set  @StartingPosition = dbo.CHARINDEX_BIN( reverse(@expression1), reverse(@expression2), @start_location)

     return case 
               when  @StartingPosition > 0
               then  1 - @StartingPosition + datalength(@expression2)/(case SQL_VARIANT_PROPERTY(@expression2,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode  
            else 0 
            end
    end
GO
-- Is similar to the built-in function Transact-SQL charindex, but regardless of collation settings,  
-- executes case-sensitive search  
-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
CREATE function CHARINDEX_BIN(@expression1 nvarchar(4000), @expression2  nvarchar(4000), @start_location  smallint = 1)
returns nvarchar(4000)
as
    begin
       return charindex( cast(@expression1 as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(@expression2  as nvarchar(4000)) COLLATE Latin1_General_BIN, @start_location )
    end
GO



I'm good programmer,
no, no, no I'm average programmer
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000