SQL String Functions

1. Convert to uppercase String upper

The upper function is used to return all strings to uppercase characters.

return value:

varchar or nvarchar, variable-length string.

Example:

Select Upper (' ABCDEFG ')    --output ABCDEFG Notice that it's all converted to uppercase.

　

2. Convert to lowercase string lower

The lower function returns after all the strings have been converted to lowercase characters.

return value:

varchar or nvarchar, variable length string

Example:

Select lower (' ABCDEFG ')    --The output ABCDEFG has all been converted to lowercase

3. Clear the left space LTrim

If the character of a string is a space, the LTrim function is used to clear contiguous left spaces.

varchar or nvarchar, variable-length string.

Example:

Select LTrim ('    123456789 ')  --output 123456789, note that the left-hand space has been deleted, the output string to the left is no space

　

4. Clear the right space RTrim

The RTrim function is used to empty contiguous spaces on the right.

varchar or nvarchar, variable-length string.

Example:

Select RTrim (' 123456789   ')    --output 123456789, note that the right space has been cleared

　

5. Intercept string substring

The SUBSTRING function can intercept strings in a string.

Example:

Select substring (' Hello ')    --Output He     is a little different from C #, and its first character starts with 1.

6. Get the string length len

The Len function is used to get the length (number of characters) of a string, but does not include the right space. The left space and the space on the right are counted.

　　　

Example:

Select Len (' The big, all the world ')    --Output 9

　

　14. Truncate left string

The left function is used to intercept a string of the specified length, starting with the first character on the other. Its execution effect equals substring (expression,1,length)

varchar or nvarchar, variable-length string.

Example:

Select Left (' 123456789 ', 3)    --Output 123

　　　15. Truncate RIGHT string

The right function is used to intercept a string of the specified length starting with the first character on the left. Its execution effect equals sunstring (Expression,len (expression)-length+1,length).

varchar or nvarchar, variable-length string.

Example:

Select Right (' 123456789 ', 3)    --Output 789

　　　16. Clear the left space LTrim

If the character of a string is a space, the LTrim function is used to clear contiguous left spaces.

varchar or nvarchar, variable-length string.

Example:

Select LTrim ('    123456789 ')  --output 123456789, note that the left-hand space has been deleted, the output string to the left is no space

　　17. Clear the right space RTrim

The RTrim function is used to empty contiguous spaces on the right.

varchar or nvarchar, variable-length string.

Example:

Select RTrim (' 123456789   ')    --output 123456789, note that the right space has been cleared

　　23. REPLACE

Replaces all occurrences of the specified string value with another string value.

Example:

SELECT replace (' ABCDE ', ' abc ', ' xxx ')    --xxxde     ABCDE is going to look for those characters (ABCDE)   ABC is going to replace the ABC    XXX is to replace what

SQL String Functions