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

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)