UDFs CHRTRAN, STRFILTER, - 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 CHRTRAN, STRFILTER,

Author

Topic

Igor2004
More clever than you

78 Posts

Posted - 2005-06-17 : 22:54:24

[code]
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- CHRTRAN() User-Defined Function
-- Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
-- CHRTRAN (@cExpressionSearched, @cSearchExpression, @cReplacementExpression)
-- Return Values nvarchar
-- Parameters
-- @cSearchedExpression Specifies the expression in which CHRTRAN( ) replaces characters.
-- @cSearchExpression Specifies the expression containing the characters CHRTRAN( ) looks for in @cSearchedExpression.
-- @cReplacementExpression Specifies the expression containing the replacement characters.
-- If a character in cSearchExpression is found in cSearchedExpression, the character in @cSearchedExpression is replaced by a character from @cReplacementExpression
-- that is in the same position in @cReplacementExpression as the respective character in @cSearchExpression.
-- If @cReplacementExpression has fewer characters than @cSearchExpression, the additional characters in @cSearchExpression are deleted from @cSearchedExpression.
-- If @cReplacementExpression has more characters than @cSearchExpression, the additional characters in @cReplacementExpression are ignored.
-- Remarks
-- CHRTRAN() translates the character expression @cSearchedExpression using the translation expressions @cSearchExpression and @cReplacementExpression and returns the resulting character string.
-- CHRTRAN is similar to a function Oracle PL/SQL TRANSLATE
-- Example
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Displays XBYDZF
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Displays XBYDZF
-- See Also STRFILTER()
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
/* -- this algorithm does not work as
-- select dbo.CHRTRAN2('eaba','ba','a') -- Displays e Error !!!
-- select dbo.CHRTRAN('eaba','ba','a') -- Displays ea Correctly
while @i <= @len
select @cExpressionSearched = replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN,
cast(substring(@cSearchExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN,
cast(substring(@cReplacementExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN ) ,
@i = @i + 1

return @cExpressionSearched
*/
CREATE function CHRTRAN (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(256), @cReplacementExpression nvarchar(256))
returns nvarchar(4000)
as
begin
declare @len smallint, @i smallint, @j smallint, @cExpressionTranslated nvarchar(4000)
select @cExpressionTranslated = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

while @i <= @len
begin
select @j = dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default)
if @j > 0
select @cExpressionTranslated = @cExpressionTranslated + substring(@cReplacementExpression, @j , 1)
else
select @cExpressionTranslated = @cExpressionTranslated + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @cExpressionTranslated
end
GO

-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- STRFILTER() User-Defined Function
-- Removes all characters from a string except those specified.
-- STRFILTER(@cExpressionSearched, @cSearchExpression)
-- Return Values nvarchar
-- Parameters
-- @cExpressionSearched Specifies the character string to search.
-- @cSearchExpression Specifies the characters to search for and retain in cString.
-- Remarks
-- STRFILTER( ) removes all the characters from @cExpressionSearched that are not in @cSearchExpression, then returns the characters that remain.
-- Example
-- select dbo.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789') -- Displays 516378306
-- select dbo.STRFILTER('ABCDABCDABCD', 'AB') -- Displays ABABAB
-- See Also CHRTRAN()
-- UDF the name and functionality of which correspond to the same functions of Foxtools ( Foxtools is a Visual FoxPro API library)
CREATE function STRFILTER (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @len smallint, @i smallint, @StrFiltred nvarchar(4000)
select @StrFiltred = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

while @i <= @len
begin
if dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) > 0
select @StrFiltred = @StrFiltred + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end

return @StrFiltred
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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources