UDFs AT, RAT, OCCURS, OCCURS2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

UDFs AT, RAT, OCCURS, OCCURS2

Author

Topic

Igor2004
More clever than you

78 Posts

Posted - 2005-06-17 : 22:42:38

[code]

-- 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

[/code]

I'm good programmer,
no, no, no I'm average programmer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources