String User-Defined Functions



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

    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)


    »


    See All Articles by Columnist
    Alexander Chigrik

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Latest Articles