SQL bit by bit 33-sql string operations

Calculate the string length
Len () is used to calculate the length of a string.

select sname ,len(sname) from student

Converts a string to uppercase or lowercase.
Lower () is used to convert a string to lowercase, and upper () is used to convert a string to uppercase.

select lower('I AM A STUDENT !')select upper('i am a student !')

Truncates spaces on the left and right of the string.
Ltrim () is used to remove spaces on the left side of a string, and rtrim () is used to remove spaces on the right side of a string.

Declare @ STR varchar (100) set @ STR = 'I have spaces on the left! 'Select @ STR as initial character, Len (@ Str) as initial length, ltrim (@ Str) as existing character, Len (ltrim (@ Str) as existing Length

Returns a string consisting of repeated spaces.

Space (integer_expression) integer_expression indicates a positive integer in the number of spaces. If integer_expression is negative, an empty string is returned. Select 'A' + space (2) + 'B'

Substring

Substring (string, start_position, length) can be a substring of any length from any position, left (string, length) from the left side to get the substring right (string, length) select substring ('helloworld! ', 6, 6) Select left ('helloworld! ', 5) select right ('helloworld! ', 6)

String replacement

Replace (string, the string to be replaced, the string to be replaced) Select Replace ('helloworld! ', 'O', 'E') the result is hellewerld!

Returns the reverse value of the string value.

Reverse (string_expression) Select reverse ('abc') Result: CBA

Deletes a character of the specified length and inserts another character group at the specified start point.

Stuff (character_expression, start, length, character_expression) Start is an integer that specifies the start position for deletion and insertion. Length is an integer that specifies the number of characters to be deleted. Select stuff ('abcdefg', 'Hello'): Hello g

Repeat the string value with a specified number of times

Replicate (string_expression, integer_expression) Select replicate ('abc', 4) Result: abcabcabcabc

Returns the starting position of the specified expression in the string.

Charindex (expression1, expression2, start_location) or charindex (expression1, expression2) expression1 in expression2 start position select charindex ('h', 'ellohworld') Result: 5

Returns the starting position of the first occurrence of a pattern in the specified expression, and returns the starting position of the first occurrence of a pattern in the specified expression;

patindex('%pattern%',expression)

If this mode is not found in all valid text and character data types, zero is returned.

Select patindex ('% Hello %', 'worldhello'): 6

Returns the integer of the first character of the input expression.

Unicode ('ncharacter _ expression') 'ncharacter_expression' is an nchar or nvarchar expression. Select Unicode ('A') Result: 97 select Unicode ('abc') Result: 97

Returns the character data converted from numeric data.
STR (float_expression, length, decimal)
Float_expression an expression of the approximate number (float) data type with the decimal point.
The total length of length. It includes the decimal point, symbol, number, and space. The default value is 10.
Decimal: the number of decimal places on the right of the decimal point. Decimal must be less than or equal to 16. If the decimal value is greater than 16, the result is truncated to 16 digits on the right of the decimal point.

Select STR (123.436, 2), Len (STR (123.436, 2) // when the expression exceeds the specified length, the string returns the ** select STR (123.436) for the specified length ), len (STR (123.436), STR (123.436, 6), Len (STR (123.436, 6), STR (123.436, 6, 1), Len (STR (123.436, 6, 1) // converts an expression consisting of six digits and a decimal point to a string with six locations. The fractional part of A number is rounded to a decimal place. Select STR (1234.436), Len (STR (1234.436), STR (1234.436, 6), Len (STR (1234.436, 6), STR (1234.436, 6 ), len (STR (1234.436 ))

Get the ASCII code of the character
ASCII () is used to obtain the ASCII code of a character. It has only one parameter. If the parameter is a string, the ASCII code of the first character is obtained.

select ascii('H')select ascii('HelloWorld!')

Get a character corresponding to an ASCII number.

Char (integer_expression) integer between 0 and 255. If this integer expression is not in this range, null is returned. Select char (72)

Returns the Unicode character with the specified integer code.

Nchar (integer_expression) integer_expression is a positive integer between 0 and 65535. If a value out of this range is specified, null is returned. Select nchar (1, 1000)

Returns a unicode string with delimiters. Adding delimiters can make the input string a valid SQL Server separator identifier.

Quotename ('character _ string') character_string cannot exceed 128 characters. If the input exceeds 128 characters, null is returned. The result of select quotename ('abc [AA] def ') is [ABC [] def]. Note that there are two right brackets in the string ABC [] def to indicate escape characters. Select quotename ('abcdef', ''') -- The separator is two single quotes -- 'abcdef' select quotename ('abcdef ') -- Delimiter is] -- [abcdef] Select quotename ('abcdef ',' {} ') -- Delimiter is} -- {abcdef}

Pronunciation matching degree
Sometimes we don't know the spelling of a person's name, but we know his pronunciation. Then we can test the matching degree of pronunciation.
Soundex () is used to calculate the pronunciation characteristics of a string. This feature value is a four-character string. the first character of the feature is always the first character in the initial string, then there is a three-digit number.
Select sname, soundex (sname) from student
The meanings of pronunciation feature values are very complex. It is very troublesome to analyze the pronunciation similarity between two strings based on the two pronunciation feature values.
Difference () can be used to simplify the pronunciation Similarity comparison between two strings. It can calculate the pronunciation feature values of the two strings and compare them,
Then return a value ranging from 0 ~ The value of 4 indicates the pronunciation similarity between two strings. A larger value indicates a greater pronunciation similarity between two strings.
Select sname, soundex (sname), difference (sname, 'herry') from Stu