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.

    Get the Free Newsletter!

    Subscribe to Cloud Insider for top news, trends & analysis

    Latest Articles