String User-Defined Functions

Introduction

String UDFs

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:
__ATLAS_TABLE_BLOCK__0__
Examples
This example returns a character string created by inserting the
second string starting at position 2 (at b) into the first string.
__ATLAS_TABLE_BLOCK__1__
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:
__ATLAS_TABLE_BLOCK__2__
Examples
This example returns a character string created by deleting three
characters from the first string (abcdef) starting at position 2
(at b).
__ATLAS_TABLE_BLOCK__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
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:
__ATLAS_TABLE_BLOCK__4__
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).
__ATLAS_TABLE_BLOCK__5__
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:
__ATLAS_TABLE_BLOCK__6__
Examples
__ATLAS_TABLE_BLOCK__7__
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:
__ATLAS_TABLE_BLOCK__8__
Examples
__ATLAS_TABLE_BLOCK__9__
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:
__ATLAS_TABLE_BLOCK__10__
Examples
__ATLAS_TABLE_BLOCK__11__
Here is the result set:
——————-
129876543
(1 row(s) affected)

»


See All Articles by ColumnistAlexander Chigrik