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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 28, 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