SQL Server String Functions

String functions are used to perform various operations on characters and binary characters

1.ASCII () function

The ASCII (character_expression) function is used to return the leftmost ASCII code value in a string expression. The parameter character_expression must be a string expression of char or varchar type.
Eg:select ASCII (' s '), ASCII (' SQL '), ASCII (' 1 ');
Execution Result:
The ASCII value of the character ' s ' is 115, so the first and second rows return the same result, and for a string of pure numbers in the third statement, it can be enclosed in single quotes.

2.CHAR () function

The CHAR (integer_expression) function converts the ASCII value of an integer type to the corresponding character, and integer_expression is an integer between 0~255. If the integer expression is not within this range, a null value is returned.
Eg:select char (+), char (49);
As you can see, the value returned here is exactly the opposite of the return value of the ASCII function.

3.LEFT () function

The left (character_expression,integer_expression) function returns a string, character, or binary data expression that starts with the specified number of characters to the ieft of the string. Character_expression is a string expression, which can be a constant, a variable, or a field. Integer_expression is an integer that specifies the number of characters that character_expression will return.
Eg:select left (' Football ', 4);
The function returns a substring of length 4 starting at the left of the string "Football", with the result "foot", and the visible index starting from 1.

4.RIGHT () function

In contrast to the left () function, right (character_expression,integer_expression) returns the rightmost integer_expression character of the string character_expression.
Eg:select right (' Football ', 4);
The function returns a string of length 4 starting at the right of the character channeling "football", with the result "ball" and the index starting from 1.

5.LTRIM () function

LTRIM (character_expression) is used to remove extra spaces to the left of a string, character data expressions character_expression is a string expression, can be a constant, a variable, or it can be a character field or a binary data column.
Eg:select ' (' + ' book ' + ') ', ' (' +ltrim (' book ') + ') ';
Comparing two values, LTrim only removes the space to the left of the string, and the space on the right is not deleted.

6.RTRIM () function

RTRIM (character_expression) is used to remove extra spaces to the right of the string, and the character data expression character_expression is a string expression, which can be a constant, a variable, a character field, or a binary data column.
Eg:select ' (' + ' book ' + ') ', ' (' +rtrim (' book ') + ') ';
Comparing two values, LTrim only removes the space to the right of the string, and the left space is not deleted.

7.STR () function

The STR (float_expression [, length [, decimal]]) function is used to convert numeric data to character data. Float_expression is an expression of an approximate numeric (float) data type with a decimal point. Length indicates the total lengths. It includes decimal points, symbols, numbers, and spaces, with the default value of 10. decimal specifies the number of digits after the decimal point, and decimal must be less than or equal to 16. If the decimal is greater than 16, the result is truncated so that it remains 16 after the decimal point.
Eg:select Str (3141.59,6,1), str (123.45,2,2)
The first statement consists of 6 digits and a decimal point of 3141.59 converted to a string of length 6, the decimal portion of the number is rounded to 1 decimal digits, and the expression in the second statement exceeds the specified total length, and the string returned is two * * for the specified length.

8. Inverse string function reverse ()

REVERSE (s) reverses the string s, and the order of the returned strings is reversed in the order of S.
Eg:select REVERSE (' abc ');
As the result can be seen, the string "abc" after the reverse function processing, all the string order is reversed, the result is "CBA";

9. Length function for calculating strings len (str)

Returns the number of characters in a character expression. If the string contains leading and trailing spaces, the function includes them. Len returns the same value for the same single-byte and double-byte strings.
Eg:select len (' No '), Len (' Date '), Len (12345);
As you can see, the Len function returns the same length of string when it treats both English and Chinese characters, and a Chinese character counts as one. The Len function treats a pure number as a string, but a pure number can be a non-functional quotation mark.

10. Function Charindex matching the starting position of the string (Str1,str,[start])

The CHARINDEX (Str1,str,[start]) function returns the substring str1 at the beginning of the string str, starting at the start of the search, and searching from the specified position if the start parameter is specified If you do not specify a start parameter or specify 0 or a negative value, search from the beginning of the string.
Eg:select CHARINDEX (' A ', ' banana '), CHARINDEX (' A ', ' banana ', 4), CHARINDEX (' na ', ' banana ', 4);

CHARINDEX (' A ', ' banana ') returns the first occurrence of a string ' banana ' neutron string ' a ', with a result of 2;
CHARINDEX (' A ', ' banana ', 4) returns the position of the substring ' a ' from the 4th position in the string ' banana ', resulting in 4;
CHARINDEX (' na ', ' banana ', 4) returns the first occurrence of the substring ' na ' starting at position 4th, resulting in 5

11.SUBSTRING () function

The SUBSTRING (value_expression,start_expression,length_expression) function returns a character expression, a binary expression, a text expression, or part of an image expression.
Value_expression is a character, binary, text, ntext, or image expression.
start_expression specifies an integer or an expression that returns the starting position of the character. If Start_expression is less than 0, or generates an error and terminates the statement. If Start_expression is greater than the number of characters in the value expression, a 0-length expression is returned.
Length_expression is a positive integer or a character number expression that specifies the value_expression to return. If Length_expression is negative, an error is generated and the statement is terminated, and if the sum of start_expression and Length_expression is greater than the number of characters in value_expression, the entire value expression is returned.
Eg:select SUBSTRING (' breakfast ' 1,5), SUBSTRING (' Breakfast ', Len (' Breakfast ')/2, Len (' Breakfast '));
The first statement returns a string with a length of 5 starting at the first position, the result is "break", and the second statement returns the string of the entire string, with the result "Akfast"

12.LOWER ()

LOWER (character_expression) returns a character expression after converting the uppercase character data to lowercase characters. Character_expression is the string that specifies the conversion to be made.
Eg:select LOWER (' BEAUTIFUL '), LOWER (' well ');
As the result can be seen, after the lower () function is converted, the uppercase letters are lowercase and the lowercase letters remain unchanged.

13.UPPER () function

UPPER (character_expression) returns a character expression after converting lowercase character data to uppercase characters. character_expression specifies the string to be converted.
Eg:select UPPER (' black '), UPPER (' black ');
As the result can be seen, after the upper function is converted, the lowercase letters become uppercase and the uppercase letters remain unchanged.

14. Replacing function replace (S,S1,S2)

Replace (S,S1,S2) uses the string S2 to replace the S1 in the string s.
Eg:select REPLACE (' xxx.sqlserver2012.com ', ' x ', ' W ');
Eplace (' xxx.sqlserver2012.com ', ' x ', ' W ') replaces ' x ' in the string "xxx.sqlserver2012.com" with the ' W ' character, resulting in "www.sqlserver2012.com";

SQL Server String Functions