Binaryworld - String User-Defined Functions ... [ SQL Server -> User Defined Function ]

Introduction
String UDFs
StrIns
StrDel
StrSeparate
StrCHARINDEX
StrREPLACE
StrREVERSE

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

==>Date and Time User-Defined Functions
==>Mathematical User-Defined Functions
==>Metadata User-Defined Functions
==>Security User-Defined Functions
==>String User-Defined Functions
==>System User-Defined Functions
==>Text and Image User-Defined Functions

In this article, I wrote some useful String User-Defined Functions.

String UDFs

These scalar User-Defined Functions perform an operation on a string input value and return a string or numeric value.

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:

Click here to copy the following block

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.

Click here to copy the following block

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:

Click here to copy the following block

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

Click here to copy the following block

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:

Click here to copy the following block

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

Click here to copy the following block

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:

Click here to copy the following block

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

Click here to copy the following block

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:

Click here to copy the following block

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

Click here to copy the following block

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:

Click here to copy the following block

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

Click here to copy the following block

SELECT dbo.StrREVERSE('123456789', 3)

Here is the result set:

-------------------
129876543

(1 row(s) affected)