
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

String User-Defined Functions

Alexander Chigrik
chigrik@mssqlcity.com

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

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')

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 - an expression of character data. character_expression can be a constant, variable, or column of character data.

start - 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 - 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)

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 - an expression of character data. character_expression can be a constant, variable, or column of character data.

term - a character.

number - 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)

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 - an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.

expression2 - an expression, usually a column searched for the specified sequence. expression2 is of the character string data type category.

start_location - 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 - 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)

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' - the string expression to be searched.

'string_expression2' - the string expression to try to find.

'string_expression3' - the replacement string expression.

start_location - 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)

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 - an expression of character data. character_expression can be a constant, variable, or column of character data.

start_location - 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)

Here is the result set:

-------------------
129876543

(1 row(s) affected)

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc