String User-Defined Functions

December 27, 2000

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
• Security User-Defined Functions
• String User-Defined Functions
• System User-Defined Functions
• Text and Image 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)

```