String functions of SQL Server common system functions (I.)

It's been a long time since I wrote a blog, I'm going to write a string function

QQ Group: 499092562; Welcome to Exchange

String functions:

1, LEN (need to get the length of the string)

Return: Length of string

Example:

SELECT LEN (' The little porter is very handsome! ‘)

2. Right (the string that needs to be intercepted from the left, intercept the starting subscript, intercept the length)

Return: The string to the right

Example:

SELECT Right (' small porter ', 2,2)

' Little Porter ' starts with a second subscript on the right and a string of length two

3, CHARINDEX (the parent string, the substring that needs to find the location, starting from where to find)

Example: SELECT CHARINDEX (' Small porter ', ' work ', 1)

Find the ' work ' word in the ' little Porter ' from the first location

4, LTRIM (the string that needs to be emptied);

Returned: The string that was emptied to the left space

Example:

SELECT LTRIM (' small porter ')

5, RTRIM (need to be emptied to the right of the string)

Returns: The string that is emptied to the right of the space

6, STUFF (string, start subscript, intercept length, insert string)

In a string, delete the character of the specified length and insert a new string at that location;

Example

SELECT STUFF (' ABCDEFG ', 2, 3, ' small movers ')

Return: a small porter EFG

7. Replace (Specify string, string to be replaced, string to replace)

Returns a new string

Example:

SELECT REPLACE (' Small porter ', ' small ', ' big ')
Return: Big Porter

8.

UPPER (strings that need to be converted to uppercase)

Converts the string passed to it to uppercase

SELECT UPPER (' SQL Server Course ')

Back to: SQL Server Course

9, SUBSTRING (string, starting subscript, length)

Extracting a string

Example

SELECT SUBSTRING (' Small Porter ', 2, 3)

return: Porter

10 . Return the inverse value of the string value
REVERSE (string that requires inverse value)

Example:

SELECT REVERSE (' small porter ')

Back to the labor movement to move small

11 . Repeat string values for a specified number of times
REPLICATE (requires duplicate string, repeat Count)

Example:

SELECT REPLICATE (' Small Porter ', 3)

Back small Porter small Porter small Porter

12. Returns the integer value of the first character of an input expression

UNICODE (a string that needs to return the first character integer value)

' need to return First character The integer value of the string ' is an nchar or nvarchar expression.

Example:

SELECT UNICODE (' small ')

Returns: 23567
SELECT UNICODE (' small porter ')

Returns: 23567

13.

Pronunciation Matching degree
SOUNDEX () is used to calculate the pronounced characteristics of a string,

Returns a four-character string,

And the first character of the return value is always the first character in the initial string,

And then a three-digit number.

SELECT Stuname,soundex (stuname) from Stu_info

The result is:

Q Q000
Q Q000
W 0000
E E000
R R000
T T000
Y 0000
U U000
II I500
777 0000
78 0000
H H000
H H000
B B000

The meaning of the pronounced eigenvalues is very complex, and it is difficult to use SOUNDEX () if you want to analyze the pronunciation similarity of two strings based on two pronounced eigenvalues.
Then difference () is a good choice, it can calculate the pronunciation eigenvalues of two strings, and compare them to simulate the similarity degree
A value of 0 to 4 is then returned to reflect the pronunciation similarity of two strings, and the larger the value, the higher the pronunciation similarity of the two strings.

SELECT Stuinfoq,soundex (stuname), Difference (stuname, ' H ') from Stu_infoThe result is:

Q Q000 3
Q Q000 3
W 0000 3
E E000 3
R R000 3
T T000 3
Y 0000 3
U U000 3
II I500 2
777 0000 3
78 0000 3
H H000 4
H H000 4
B B000 3

Next time Add, hey

String functions of SQL Server common system functions (I.)