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.)