SQL string manipulation functions

1, go to the space function

(1), LTRIM () remove the space at the head of the string.

(2), RTRIM () remove the space at the tail of the string.

2. Character conversion function
(1), ASCII ()
Returns the ASCII value of the leftmost character of a character expression. In the ASCII () function, a string of pure numbers is not "enclosed", but a string with other characters must be used in ' surround ', or an error will occur.
(2), CHAR ()
Converts an ASCII code to a character. If you do not enter an ASCII value between 0 and 255, CHAR () returns NULL.
(3), LOWER () and Upper ()
LOWER () converts all strings to lowercase, UPPER () capitalizes all strings.
(4), STR ()
Converts numeric data to character data.
STR (<float_expression>[,length[, <decimal>])
length specifies the size of the returned string, and decimal specifies the number of decimal digits to return. If the length is not specified, the default value is ten, and the decimal default is 0.
Returns null when length or decimal is a negative value;
When length is less than the number of digits to the left of the decimal point (including the sign bit), the length of * is returned;
Obey length first, then take decimal;
When the number of string bits returned is less than length, the left side complements the space.

3, take the SUBSTRING function

(1), return the section starting_ position characters character length from the left of the string

SUBSTRING (<expression>, <starting_ position>, length)

Ex

DECLARE @str VARCHAR (200)
SET @str = ' Howareyoumyboy '
SELECT SUBSTRING (@str, 2, 3)

Output: OWA

(2), return character_expression integer_expression characters from right

Right (<CHARACTER_EXPRESSION>, <integer_expression>)

Ex

DECLARE @str VARCHAR (200)
SET @str = ' Howareyoumyboy '
SELECT Right (@str, 3)

Output: Boy

(3), return character_expression left integer_expression characters

Left (<CHARACTER_EXPRESSION>, <integer_expression>)

Ex

DECLARE @str VARCHAR (200)
SET @str = ' Howareyoumyboy '
SELECT Left (@str, 3)

Output: How

4. String comparison function

(1), CHARINDEX ()
Returns the beginning of the occurrence of a specified substring in a string.
CHARINDEX (< ' substring_expression ';, <expression>,<start_location>)
Where substring _expression is the character expression to look for, expression can be a string or a column name expression, Start_location is the location to begin the lookup. If no substring is found, a value of 0 is returned.

Ex

DECLARE @str VARCHAR (200)
SET @str = ' Howareyoumyboy '
SELECT CHARINDEX (' is ', @str)

Output: 4

This function cannot be used with text and image data types.

(2), PATINDEX ()
Returns the beginning of the occurrence of a specified substring in a string.
PATINDEX (< '%substring _expression% ';, <column_ name>) where the substring expression must have a percent semicolon "%" or the return value is 0.
Unlike the CHARINDEX function, wildcard characters can be used in substrings of the PATINDEX function, and this function can be used with char, VARCHAR, and text data types.

Ex

DECLARE @str VARCHAR (200)
SET @str = ' Howareyoumyboy '
SELECT PATINDEX ('%you% ', @str)

Output: 7

EX2:

SELECT PATINDEX ('%te% ', name) from Dbo.item

5. String manipulation functions
(1), QUOTENAME ()
Returns a string that is enclosed by a specific character.
QUOTENAME (< ' character_expression ' >[, quote_ character]) where Quote_ character the character used to enclose the string, the default value is "[]".

Ex

DECLARE @str VARCHAR (200)
SET @str = ' Howareyoumyboy '

Select QUOTENAME (@str, ' "')

Output: "Howareyoumyboy"

SELECT QUOTENAME (@str, "")

Output: ' Howareyoumyboy '

SELECT QUOTENAME (@str)

Output: [Howareyoumyboy]

(2), REPLICATE ()
Returns a string that repeats character_expression the specified number of times.
REPLICATE (character_expression integer_expression) returns null if the Integer_expression value is negative.

Ex

DECLARE @str VARCHAR (200)
SET @str = ' Howareyoumyboy '

SELECT REPLICATE (@str, 2)

Output: Howareyoumyboyhowareyoumyboy

(3), REVERSE ()
Reverses the character order of the specified string.
REVERSE (<character_expression>) where character_expression can be a string, a constant, or a column's value.

Ex

DECLARE @str VARCHAR (200)
SET @str = ' howareyou '

SELECT REVERSE (@str)

Output: Uoyerawoh

SELECT REVERSE (name) from Dbo.item

(4), REPLACE ()
Returns the string that was replaced by the specified substring.
REPLACE (<STRING_EXPRESSION1>, <string_expression2>, <string_expression3>) with String_expression3 Replace the substring string_expression2 in the string_expression1.

Ex

DECLARE @str VARCHAR (200)
SET @str = ' howareyou '

SELECT REPLACE (@str, ' is ', ' LSG ')

Output: howlsgyou

(5), SPACE ()

Returns a blank string with the specified length.
SPACE (<integer_expression>) returns null if the Integer_expression value is negative.

(6), STUFF ()
Specifies a substring of the position, length, with another substring substitution string.
STUFF (<character_expression1>, <start_ Position>, <length>,<character_expression2>)
A null value is returned if the starting position is negative or the length value is negative, or the starting position is greater than the length of character_expression1.
If length is greater than start_ position in Character_expression1 to the right length, Character_expression1 retains only the first character.

Ex

DECLARE @str VARCHAR (200)
SET @str = ' howareyou '

SELECT STUFF (@str, 4,3, ' LSG ')

Output: howlsgyou

6.SQL wildcard characters

The SQL wildcard can override one or more characters when searching for data in a database. SQL wildcard characters must be used with the LIKE operator. In SQL, you can use the following wildcard characters:

wildcard characters	Description
%	Replace 0 or more characters
_	Replaces only one character
[Charlist]	Any single character of the word columns
[^charlist] or [!charlist]	Any single character that is not in the word columns

SQL string manipulation functions