String User-Defined Functions

Introduction

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

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
Is an expression of character data. character_expression can be a

constant, variable, or column of character data.
start
Is 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’)

GO






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
Is an expression of character data. character_expression can be a

constant, variable, or column of character data.
start
Is 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
Is 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)

GO






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
Is an expression of character data. character_expression can be a

constant, variable, or column of character data.
term
Is a character.
number
Is 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)

GO






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
Is an expression containing the sequence of characters to be found.

expression1 is an expression of the short character data type category.
expression2
Is an expression, usually a column searched for the specified sequence.

expression2 is of the character string data type category.
start_location
Is 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
Is 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)

GO






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’
Is the string expression to be searched.
‘string_expression2’
Is the string expression to try to find.
‘string_expression3’
Is the replacement string expression.
start_location
Is 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)

GO






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
Is an expression of character data. character_expression can be

a constant, variable, or column of character data.
start_location
Is 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)

GO






Here is the result set:
——————-

129876543
(1 row(s) affected)