This article mainly introduces a summary of the common functions of SQL Server. Friends who need it can refer to it.
I wanted to record some common functions of SQL before, but it has never been implemented. . . hey-hey. . .
Until today, I have used the substring() function. The starting value of this method in C# is 0, while the starting value in SQL is 1. stupidly can not tell. . .
This blog is used to record the use of SQL functions. I will write wherever I think of using it. . .
SubString(): Method used to intercept the specified string. This method has three parameters:
Parameter 1: used to specify the string to be operated on.
Parameter 2: used to specify the starting position of the string to be intercepted, the starting value is 1.
Parameter 3: used to specify the length to be intercepted.
1 2 |
|
Left(): Used to return the left part of the specified length in the specified string. This method has two parameters:
Parameter 1: used to specify the string to be operated on.
Parameter 2: used to specify the length of the substring to be returned.
1 2 |
|
Right(): Used to return the right part of the specified length in the specified string. This method has two parameters:
Parameter 1: used to specify the string to be operated on.
Parameter 2: used to specify the length of the substring to be returned.
1 2 |
|
CharIndex(): Used to return the starting position of the specified substring in the specified string. Returns 0 if not found. This method has two parameters:
Parameter 1: used to specify the string to be searched.
Parameter 2: used to specify the string used for retrieval.
1 2 3 |
|
Stuff(): Used to delete characters of a specified length and insert new characters/values at the deleted positions. This method has four parameters:
Parameter 1: used to specify the string to be operated on.
Parameter 2: used to specify the starting position of the characters to be deleted.
Parameter 3: used to specify the length of characters to be deleted.
Parameter 4: Used to specify the new string/value inserted at the deleted position.
1 2 |
|
Len(): Used to return the length of the value of the specified text. Leading spaces are counted, trailing spaces are not. This method has one parameter:
Parameter 1: used to specify the text or string to be operated on.
1 2 |
|
Difference(): Used to return an integer value indicating the difference between the SOUNDEX values of two character expressions. (i.e. the similarity of two strings) So what is a SOUNDEX value? Remember it first, it's its turn next.
The returned value ranges from 0 to 4: 0 means almost different or completely different, 4 means almost the same or exactly the same. This method has two parameters:
Parameter 1: used to specify the first string SOUNDEX value to be compared.
Parameter 2: used to specify the second string SOUNDEX value to be compared.
1 2 |
|
Soundex(): used to return the SOUNDEX value of the specified string. SOUNDEX is a phonetic algorithm that uses the pronunciation of English words to calculate approximate values. The value consists of four characters, the first character is an English letter, and the last three are numbers. In Pinyin text, sometimes you can pronounce but cannot spell the correct word. You can use Soundex to achieve a similar fuzzy matching effect. The fuzzy matching here is different from LIKE.
Brief description of the algorithm:
1 2 3 4 5 6 7 8 |
|
If there are 2 or more letters with the same corresponding numbers in the string together (such as j and k) , delete the others and keep only 1. Remove the characters whose corresponding value is 0, and only return the first 4 bytes, which are not enough to fill with 0.
1 2 3 4 |
|
PS: Characters other than English characters will return 0000, so the second example of the method Difference() above will return 4 (meaning exactly the same).
Lower(): used to return the lowercase string of the specified English string. If it is not an English string, the original value is returned. This method has one parameter:
Parameter 1: used to specify the string to be converted to lowercase.
1 2 |
|
Upper(): Used to return the uppercase string of the specified English string. If it is not an English string, the original value is returned. This method has one parameter:
Parameter 1: used to specify the string to be converted to uppercase.
1 2 |
|
Ltrim(): Used to return the string after removing leading spaces. This method has one parameter:
Parameter 1: used to specify the string to be removed from leading spaces.
1 2 3 4 |
|
Rtrim(): used to return the string after truncating trailing spaces. This method has one parameter:
Parameter 1: used to specify the string to be truncated with trailing spaces.
1 2 |
|
Replace(): Replaces all occurrences of the second given string expression in the first string expression with the third expression. This method has three parameters:
Parameter 1: used to specify the string to be operated on, that is, the string to be matched.
Parameter 2: used to specify the string to be matched.
Parameter 3: Used to specify the string used to replace existing matches.
1 2 3 4 |
|
The above is the detailed content of Summary of how to use common functions in SQL Server. For more information, please follow other related articles on the PHP Chinese website!