Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 27, 2000

String User-Defined Functions

By Alexander Chigrik


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




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM