Shulou(Shulou.com)05/31 Report--

What is the common use of SQLServer functions, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

I wanted to record some common functions of SQL before, but I never put it into practice.

Until today, the function substring () is used. The starting value of this method in C # is 0, while the starting value in SQL is 1. I can't tell the difference.

As a way to record the use of SQL functions, this blog will write wherever you want to use it.

SubString (): the method used to intercept the specified string. This method has three parameters:

Parameter 1: used to specify the string to operate on.

Parameter 2: specifies the starting position of the string to be intercepted, with a starting value of 1.

Parameter 3: used to specify the length to be intercepted.

Select substring ('abcdef',1,3)-- returns abcselect substring (' 123456321)-- returns 1, which is the first place, and it is best not to do so

Left (): returns the left portion of the specified length of the specified string. This method has two parameters:

Parameter 1: used to specify the string to operate on.

Parameter 2: used to specify the length of the substring to return.

Select LEFT ('abc123',3)-- returns abcselect LEFT (' left part, right part', 4)-- returns the left part

Right (): returns the right portion of the specified length of the specified string. This method has two parameters:

Parameter 1: used to specify the string to operate on.

Parameter 2: used to specify the length of the substring to return.

Select RIGHT ('abc123',3)-- returns 123select RIGHT (' left part, right part', 4)-- returns the right part

CharIndex (): returns the starting position of the occurrence of the specified substring in the specified string. Returns 0 if it is not found. This method has two parameters:

Parameter 1: used to specify the string to look up.

Parameter 2: used to specify the string to use for retrieval.

Select charindex ('axiomain 123a123')-return 4select charindex (' abc','123a123')-return 0select charindex ('abc','123abc123')-return 4

Stuff (): used to delete a character of a specified length and insert a new character / value at the deleted position. This method has four parameters:

Parameter 1: used to specify the string to operate on.

Parameter 2: used to specify the starting position of the character to be deleted.

Parameter 3: used to specify the length of the character to be deleted.

Parameter 4: used to specify a new string / value to be inserted at the location of the deletion.

Select stuff ('123abc456 recording recorder 4 magistrate 3 memoir ABC')-return 123ABC456select stuff ('123abc456 humorous repertoire 1 meme 3 recorder')-return abc456 and replace it with an empty string

Len (): the length of the value used to return the specified text. Leading spaces are included and trailing spaces are not counted. The method has one parameter:

Parameter 1: used to specify the text or string to operate on.

Select len ('123')-returns 3select len (' string')-returns 3

Difference (): returns an integer value indicating the difference between the SOUNDEX values of two character expressions. (that is, the similarity between two strings) what is the SOUNDEX value? Remember, it's his turn next.

The returned values range from 0 to 4: 0 means almost or completely different, and 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.

Select difference ('action','demo')-- returns 2select difference (' 123456 'integer')-- returns 4

Soundex (): returns the SOUNDEX value of the specified string. SOUNDEX is a phonetic algorithm that uses the pronunciation of English words to calculate approximate values. The values are made up of four characters, the first character being English letters and the last three numbers. Sometimes there are situations in which you can pronounce but cannot spell the correct words in the phonetic alphabet. Soundex can be used to do an effect similar to fuzzy matching. The fuzzy matching here is different from LIKE.

Brief description of the algorithm:

-- replace English words according to the following rules (do not match with the first character and do not use the value of the English character with the corresponding value of 0) an e h i o u w y-> 0 b f p v-> 1 c g j k q s x z-> 2 d t-> 3 l-> 4 mn-> 5 r-> 6

If there are two or more letters in the string that have the same corresponding number (for example, j and k), delete the others and leave only one. The characters with the corresponding value of 0 are removed and only the first 4 bytes are returned, which is not enough to fill with 0.

Select soundex ('string')-return S215select soundex (' str')-return S210select soundex ('123')-return 0000select soundex (' string')-return 0000

PS: all characters except English characters will return 0000, so the second example of the above method Difference () will return 4 (which means exactly the same).

Lower (): returns a string in lowercase form of the specified English string. If it is not an English string, the original value is returned. The method has one parameter:

Parameter 1: used to specify the string to convert to lowercase.

Select lower ('ABC')-- returns abcselect lower (' 123')-- returns 123

Upper (): a string that returns the uppercase form of the specified English string. If it is not an English string, the original value is returned. The method has one parameter:

Parameter 1: used to specify the string to be converted to uppercase.

Select upper ('abc')-- returns ABCselect upper (' 123')-- returns 123

Ltrim (): returns the string after the leading space is deleted. The method has one parameter:

Parameter 1: used to specify the string to delete leading spaces.

Select ltrim ('123')-returns 123select ltrim (' many spaces')-returns many spaces select len ('123')-returns 11select len (ltrim (' 123'))-returns 3

Rtrim (): returns the string after the trailing space is truncated. The method has one parameter:

Parameter 1: used to specify the string to truncate trailing spaces.

Select rtrim ('123')-returns 123select len (rtrim (' 123'))-returns 3

Replace (): replaces all the second given string expressions that appear in the first string expression with the third expression. This method has three parameters:

Parameter 1: used to specify the string to operate on, that is, the string to be matched.

Parameter 2: used to specify the string to match.

Parameter 3: used to specify a string that is used to replace a match that exists.

-- replace abc with xxx select replace ('123abc456parallel parallel abcwriting journal' xxx')-return 123xxx456 copyright-replace the match select replace with an empty string ('123abc456')-- will it help you to return 123456 to finish reading the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.