SQL Server built-in functions (2)-string functions

String functions are widely used in database application development. The commonly used functions are indexed as follows)
1. Str : Converts a number to a character. An optional parameter is used to specify the total length of the result (including the decimal point and the number of digits after the decimal point ).
Eg: Select STR (123.45678, 6, 2) 123.45678 is the object to be converted, 6 is the length of the converted string, and 2 is the number of digits after the decimal point.
Result: '2014. 46 '. The system automatically rounds the decimal point to 123 digits.
Another example is: Select STR (123.45678, 6, 1). In this example, the first digit after the decimal point must be retained, and a string of 6 characters must still be returned. spaces will be filled at the beginning of the result.
Result is '20140901' -- a space is at the beginning, and the length of the entire string is 6.

2. substring function : returns a part of a character, binary string, or text string.
Syntax: substring (expression, start, length) start from the start character of the expreession expression.
parameter:
expression: is a string, binary string, text, image, column, or contain column expression. Do not use expressions that contain aggregate functions.
Start: indicates the integer at the start of the substring. Start can be bigint type.
length: a positive integer that specifies the number of characters or bytes of the Expression to be returned. If length is negative, an error is returned. length can be bigint type.
eg: Select substring ('expression', 3, 4) Result: Pres
3. string substitution function
replace function: replace all specified string values with another string value.
Syntax: Replace (string_expression1, string_expression2, string_expression3)
parameter:

String_expression1:The string expression to be searched.String_expression1It can be a character or binary data type.
String_expression2:The substring to be searched.String_expression2It can be a character or binary data type.
String_expression3:Replace the string.String_expression3It can be a character or binary data type.
Eg: Select Replace ('abcdefghicde', 'cde', 'xxx'); Result: abxxxfghixxx

Stuff () function:Similar to replace. For more information, see instance.
Syntax: stuff (<character_expression1>, <start _ Position>, <length>, <character_expression2>)
Eg: select stuff ('000000', 123456789, 'aaa') Result: 1aaa56789

4. ltrim/rtrim: Delete spaces on both sides of the string. ltrim Delete left. rtrim Delete right.
5. Reverse:Returns the reverse of the specified string. eg: Select reverse ('SQL Server') Result: revres lQs
6. left/right:Returns the specified character from the left/right of the specified string. For example: Left ('SQL Server', 4) Result:' SQL '. Note that the last character is a space.
7. Len:Returns the string length. (ignore trailing space) eg: Select Len ('123456') Result: 3 Comparison: Select Len ('123456') result is 4
8. Upper/lower:Case-sensitive conversion. Upper returns all uppercase letters, and lower returns all lowercase letters.
9. Replicate ():Syntax: Replicate (character_expression integer_expression) returns a string that repeats the specified number of character_expression times. if the value of integer_expression is negative, null eg: Select replicate ('20140901', '3') Result: 123 is returned.
10. charindex function:The first parameter is the char to be searched for, and the second parameter is the string to be searched for. Return parameter 1 at parameter 2.
Eg: Select charindex ('A', 'lihan') result is 4