Reprint: SQL string manipulation function

Http://www.cnblogs.com/jiajiayuan/archive/2011/06/16/2082488.html

For example, all of the following are examples of studnet tables:

Calculating string Lengths
Len () is used to calculate the length of a string

Select Sname, Len (sname) from student

Convert string to large, 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! ')

to truncate the left and right spaces of a string
LTrim () is used to remove a space to the left of a string, and RTrim () is used to remove the space to the right of a string

DECLARE @str varchar (100)
Set @str = ' There are spaces on my 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 A positive integer that indicates the number of spaces. If integer_expression is negative, an empty string is returned.

Select ' A ' + space (2) + ' B '

Take a substring
SUBSTRING (string,start_position,length) can take substrings of any length from any location,
Left (string,length) takes a substring from the side
Right (String,length) to start with substring

Select substring (' helloworld! ', 6,6)
Select Left (' helloworld! ', 5)
Select Right (' helloworld! ', 6)

String substitution
Replace (string, strings to be replaced, strings replaced)

The result of select replace (' helloworld! ', ' o ', ' E ') is: hellewerld!

Returns the inverse value of a string value
Reverse (string_expression)

The result of select reverse (' abc ') is: CBA

Deletes a specified length of character and inserts another set of characters at the specified starting point
Stuff (character_expression, start, length, character_expression)
Start an integer value that specifies the start position of the delete and insert.
Length An integer that specifies the number of characters to delete.

Select Stuff (' ABCDEFG ', 1,6, ' hello ') results for: Hello G

Repeats a string value at a specified number of times
Replicate (String_Expression, integer_expression)

Select Replicate ( ' ABC ' , 4 ) The result is:Abcabcabcabc

Returns the starting position of the specified expression in the string
CHARINDEX (expression1, expression2, start_location) or charindex (expression1, expression2)
expression1 start position in expression2

Select CHARINDEX (' H ', ' ellohworld ') results are: 5

Returns the starting position of the first occurrence of the pattern in the specified expression
Patindex ('%pattern% ', expression) returns the starting position of the first occurrence of a pattern in a specified expression;
If the pattern is not found in all valid text and character data types, zero is returned.

Select Patindex ('%hello% ', ' Worldhello ') results are: 6

Returns an integer value of the first character of an input expression
Unicode (' ncharacter_expression ') ' ncharacter_expression ' is an expression of nchar or nvarchar.

The result of select Unicode (' a ') is: 97
The result of select Unicode (' abc ') is: 97

Returns character data converted from numeric data
STR (float_expression, length, decimal)
Float_expression An expression of an approximate numeric (float) data type with a decimal point.
Length total. It includes decimal points, symbols, numbers, and spaces. The default value is 10.
The decimal place to the right of the decimal point. decimal must be less than or equal to 16. If decimal is greater than 16, the result is truncated to the 16-bit to the right of the decimal point.

Select STR (123.436,2), Len (str (123.436,2))//When an expression exceeds a specified length, the string is returned for the specified length**

Select STR (123.436), Len (str (123.436)),
STR (123.436,6), Len (str (123.436,6)),
STR (123.436,6,1), Len (str (123.436,6,1))//An expression consisting of six digits and a decimal point is converted to a string of six positions.
The decimal portion of the 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,1), Len (str (1234.436,6,1))

The result is:

Get the ASCII code of the character
ASCII () is used to get the ASCII code of a character, it has only one parameter, if the argument is a string, then take the first character ASCII code

Select ASCII (' H ')
Select ASCII (' helloworld! ')

Get a character corresponding to the ASCII code number
Char (integer_expression) integer_expression an integer between 0 and 255. If the integer expression is not within this range, a NULL value is returned.

Select Char (72)

Returns a Unicode character that returns a specified integer code
NCHAR (integer_expression) integer_expression a positive integer between 0 and 65535. If a value beyond this range is specified, NULL is returned.

Select NCHAR (1000)

Returns a Unicode string with delimiters, in which the input string becomes a valid SQL Server delimited identifier.
QuoteName (' character_string ') character_string must not exceed 128 characters. Input of more than 128 characters will return NULL.

Select QuoteName (' Abc[aa]def ')

The result is: [abc[]]def] Note that the right parenthesis in the string Abc[]def has two, which indicates the escape character.
Select QUOTENAME (' abcdef ', ')--the delimiter is a two single quote
--' 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, and then we can make a matching test of the pronunciation.
Soundex () is used to calculate the pronounced characteristics of a string, which is a four-character string, the first character of the feature is always the first character in the initial string, and then a three-digit numeric value.

Select Sname, Soundex (sname) from student

The result is:

The meaning of the pronounced eigenvalues is very complex, and it is cumbersome to analyze the pronunciation similarity of two strings based on the two pronounced eigenvalues.
You can use difference () to simplify the pronunciation similarity comparison of two strings, which can calculate the pronounced eigenvalues of two strings, and compare them,
It then returns a value between 0~4 to reflect the pronunciation similarity of two strings, and the larger the value, the greater the pronunciation similarity of the two strings.

Select Sname,soundex (sname), Difference (sname, ' Herry ') from Stu

The result is:

Reprint: SQL string manipulation function