TSQL String Functions: Truncation and Lookup

The string truncation function refers to: Stuff and SubString, string lookup functions are: CharIndex and PatIndex

One, SubString intercept sub-string

The most commonly used string function to intercept substrings of a specific length.

SUBSTRING (expression, start, length)

Parameter description:

• Start parameter: An integer that represents the starting position; The ordinal of the character (index) starts at 1, that is, the ordinal of the first character is 1;
• Length parameter: An integer that represents the maximum number of intercepted characters, and if Start+length is greater than the total length of the string, all characters starting from Start are returned;
• Returns the data type: If expression is char or varchar, returns varchar, or nvarchar if expression is nchar or nvarchar;

For example: String abcdef, intercept starting from the second character, a total of 2 characters of the substring is: BC

Select substring ('abcdef',2,2)

Second, Stuff deletes the specified part of the string and inserts the corresponding character, replacing the specified part of the string with the new string

The stuff function deletes a string of the specified length from the starting position specified by the string, and inserts a new string from that location.

It deletes a specified length of characters in the first string at the start position and then inserts the second string I Nto the first string at the start position.

STUFF (character_expression, start, length, replacewith_expression)

Parameter description:

• Start: Integer representing the start of the deletion and insertion, or null if start is 0 or greater than the total length of the string;
• Length: An integer that represents the maximum number of characters deleted, or if Length+start is greater than the total length of the string, all characters starting from Start are deleted;
• Replacewith_expression: A character type that represents a string inserted from the start position (start);

Example: Truncating a string from a different location abcdef, viewing the returned results

Declare @str varchar(6)Set @str='abcdef'Select  Stuff(@str,7,1,"'),        Stuff(@str,0,1,"'),        Stuff(@str,3,7,"'),        Stuff(@str,3,7,'12345')

With the stuff function, you must be aware of two points:

1. If Length+start is greater than the total length of the string, all characters starting from Start are deleted;
2. Returns null if Start is 0, or is greater than the total length of the string;

Three, CharIndex look up characters from a string

Find another string from string search find, if find exists in search, returns the start position of the first match in search, if find does not exist in search, returns 0;

CHARINDEX [] )

Parameter description:

• Find the string search in string find, the start position of the lookup is determined by the parameter start;
• If the start parameter is not specified, the default starts with the first character of the string search;
• Returns null if Find or search is null;
• The return value is an integer: If find is found from search, the start position of the first match is returned, or 0 if not found;

For example: find different characters from different locations of the string abcbcdef

Select charindex('BC','Abcbcdef'),        charindex('BC','Abcbcdef',3),        charindex('BCE','Abcbcdef')

Results Analysis:

• The BC appears several times in Abcbcdef, starting with the 1th character, which returns only the start position of the first match;
• The BC appears several times in Abcbcdef, starting with the 3rd character, which returns only the start position of the first match;
• BCE does not exist in Abcbcdef, the function returns 0;

Four, PatIndex to find specific characters by pattern from a string

PatIndex finds pattern from a string, returns the start of the first match, and returns 0 if the match fails;

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is n OT found.

PATINDEX ' %pattern% ' , expression)

Pattern: Style character, capable of using wildcard characters (%,_,[],^), must start and end with a wildcard character;

Example: matching a different pattern from a string abcbcdef

Select Patindex('%bc%','Abcbcdef'),        Patindex('%b_b%','Abcbcdef'),        Patindex('%b[^c]b%','Abcbcdef'),        Patindex('%bce%','Abcbcdef')

Results Analysis:

• Pattern: %bc% represents 0 or more characters before and after BC characters
• Pattern: %b_b% indicates that there is a character between B and B with 0 or more characters before and after it
• Pattern: %b[^c]b% represents a character between B and B, which cannot be C, preceded and followed by 0 or more characters

Reference Documentation:

PATINDEX (Transact-SQL)
CHARINDEX (Transact-SQL)
SUBSTRING (Transact-SQL)
STUFF (Transact-SQL)

TSQL String Functions: Truncation and Lookup