![toolbelt[2]](http://brian.chipsofttech.com/blogs/brian/image.axd?picture=toolbelt%5B2%5D_thumb.jpg)
Using TSQL often lately, so below are a few useful User-Defined Functions grouped by the following categories. These scalar User-Defined Functions perform an operation on a string input value and return a string or numeric value. Helpful when I’m slinging data in mass quantities.
StrIns
Inserts set of characters into another set of characters at a specified starting point. Syntax
StrIns ( character_expression, start, character_expression ) Arguments
character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.
start - an integer value that specifies the location to begin insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. Return Types
nvarchar The function's text:
CREATE FUNCTION StrIns
( @str_1 nvarchar(4000),
@start int,
@str_2 nvarchar(4000) )
RETURNS nvarchar(4000)
AS
BEGIN
RETURN (STUFF (@str_1, @start, 0, @str_2))
END
GO
Examples
This example returns a character string created by inserting the second string starting at position 2 (at b) into the first string.
SELECT dbo.StrIns('abcdef', 2, 'ijklmn')
Here is the result set:
------------
aijklmnbcdef
(1 row(s) affected)
StrDel
Deletes a specified length of characters at a specified starting point.
Syntax
StrDel ( character_expression, start, length )
Arguments
character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.
start - an integer value that specifies the location to begin deletion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.
length - an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression.
Return Types
nvarchar
The function's text:
CREATE FUNCTION StrDel
( @str_1 nvarchar(4000),
@start int,
@length int )
RETURNS nvarchar(4000)
AS
BEGIN
RETURN (STUFF (@str_1 , @start, @length, ''))
END
GO
Examples
This example returns a character string created by deleting three characters from the first string (abcdef) starting at position 2 (at b).
SELECT dbo.StrDel('abcdef', 2, 3)
Here is the result set:
---
aef
(1 row(s) affected)
StrSeparate
Inserts a specified character into the given string after every n-th character (from the end of the string).
Syntax
StrSeparate ( character_expression, term, number )
Arguments
character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.
term - a character.
number - an integer.
Return Types
nvarchar
The function's text:
CREATE FUNCTION StrSeparate
( @str nvarchar(4000),
@term char(1),
@number int )
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @i int, @j int, @stepcount int
IF (len(@str) <= @number) RETURN @str
SELECT @str =REVERSE(@str), @i = 1, @j = @number + 1,
@stepcount = len(@str) / @number
WHILE @i <= @stepcount
BEGIN
SET @str = ISNULL(STUFF(@str, @j, 0, @term), @str)
SET @j = @j + @number + 1
SET @i = @i + 1
END
SET @str = REVERSE(@str)
RETURN @str
END
GO
Examples
This example returns a character string created by inserting the space character after every three characters of the specified string (from the end of the string).
SELECT dbo.StrSeparate('12345678', ' ', 3)
Here is the result set:
----------
12 345 678
(1 row(s) affected)
StrCHARINDEX
Returns the starting position of the n-th entering of the specified expression in a character string.
Syntax
CHARINDEX ( expression1, expression2, start_location, number)
Arguments
expression1 - an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.
expression2 - an expression, usually a column searched for the specified sequence. expression2 is of the character string data type category.
start_location - the character position to start searching for expression1 in expression2. If start_location is a negative number, or is zero, the search starts at the beginning of expression2.
number - an integer.
Return Types
int
The function's text:
CREATE FUNCTION StrCHARINDEX
( @expression1 nvarchar(4000),
@expression2 nvarchar(4000),
@start_location int = 0,
@number int )
RETURNS int
AS
BEGIN
DECLARE @i int, @position int
SET @i = 1
WHILE (@i <= @number) AND (CHARINDEX(@expression1, @expression2, @start_location) <> 0)
BEGIN
SET @position = CHARINDEX(@expression1, @expression2, @start_location)
SET @expression2 = STUFF(@expression2,
CHARINDEX(@expression1, @expression2, @start_location),
len(@expression1),
space(len(@expression1)))
SET @i = @i + 1
END
RETURN @position
END
GO
Examples
SELECT dbo.StrCHARINDEX('12', '2312451267124', 0, 2)
Here is the result set:
-----------
7
(1 row(s) affected)
StrREPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression starting from the start_location position.
Syntax
REPLACE('string_expression1','string_expression2','string_expression3',@start_location)
Arguments
'string_expression1' - the string expression to be searched.
'string_expression2' - the string expression to try to find.
'string_expression3' - the replacement string expression.
start_location - the character position to start replacing.
Return Types
nvarchar
The function's text:
CREATE FUNCTION StrREPLACE
( @string_expression1 nvarchar(4000),
@string_expression2 nvarchar(4000),
@string_expression3 nvarchar(4000),
@start_location int )
RETURNS nvarchar(4000)
AS
BEGIN
IF (@start_location <= 0) OR (@start_location > len(@string_expression1))
RETURN (REPLACE (@string_expression1, @string_expression2, @string_expression3))
RETURN (STUFF (@string_expression1,
@start_location,
len(@string_expression1) - @start_location + 1,
REPLACE(SUBSTRING (@string_expression1,
@start_location,
len(@string_expression1) - @start_location + 1),
@string_expression2,
@string_expression3)))
END
GO
Examples
SELECT dbo.StrREPLACE('12345678912345', '23', '**', 4)
Here is the result set:
-------------------
1234567891**45
(1 row(s) affected)
StrREVERSE
Returns the reverse of a character expression starting at the specified position.
Syntax
REVERSE ( character_expression, start_location )
Arguments
character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.
start_location - the character position to start reversing.
Return Types
nvarchar
The function's text:
CREATE FUNCTION StrREVERSE
( @character_expression nvarchar(4000),
@start_location int )
RETURNS nvarchar(4000)
AS
BEGIN
IF (@start_location <= 0) OR (@start_location > len(@character_expression))
RETURN (REVERSE(@character_expression))
RETURN (STUFF (@character_expression,
@start_location,
len(@character_expression) - @start_location + 1,
REVERSE(SUBSTRING (@character_expression,
@start_location,
len(@character_expression) - @start_location + 1))))
END
GO
Examples
SELECT dbo.StrREVERSE('123456789', 3)
Here is the result set:
-------------------
129876543
(1 row(s) affected)
StrProperCase
Returns the Proper case of a character expression starting.
Syntax
StrProperCase( character_expression)
Arguments
character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.
Return Types
nvarchar
The function's text:
CREATE FUNCTION [dbo].[StrProperCase](@Text as nvarchar(2000)) RETURNS nvarchar(2000) as
BEGIN
DECLARE @Reset bit
DECLARE @Ret nvarchar(2000)
DECLARE @i int
DECLARE @c char(1)
SELECT @Reset = 1, @i=1, @Ret = ''
WHILE @i <= LEN(@Text)
SELECT @c= SUBSTRING(@Text,@i,1),
@Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END,
@Reset= CASE WHEN
CASE WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [DOL]''' THEN 1
WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [D][I]' THEN 1
WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [M][C]' THEN 1
ELSE 0
END = 1
THEN 1
ELSE CASE WHEN @c like '[a-zA-Z]' or @c in ('''') THEN 0
ELSE 1
END
END,
@i = @i +1
RETURN @Ret
END
Examples
select dbo.strpropercase('this is a test')
This Is A Test