SQL Server common functions

SQL Server common functions

I want to record some common SQL functions before, but it has never been implemented... Hey hey...

Until today, the substring () function is used. in C #, the start value of this method is 0, while in SQL, the start value is 1. I cannot tell you clearly...

This blog is used to record SQL functions and writes them wherever they are used...

SubString (): Used to intercept a specified string. This method has three parameters:

Parameter 1: Specifies the string to be operated.

Parameter 2: Specifies the starting position of the string to be truncated. The starting value is 1.

Parameter 3: Specifies the length of the part to be intercepted.

Select substring ('abcdef ', 123456321) -- returns abcselect substring ('123456',) -- returns 1, that is, the first digit. It is recommended that you do not do this.

Left (): returns the Left part of a specified string. This method has two parameters:

Parameter 1: Specifies the string to be operated.

Parameter 2: specify the length of the substring to be returned.

Select LEFT ('abc123', 3) -- Return abcselect LEFT ('right of the LEFT part', 4) -- return the LEFT part'

Right (): returns the Right part of a specified string. This method has two parameters:

Parameter 1: Specifies the string to be operated.

Parameter 2: specify the length of the substring to be returned.

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

CharIndex (): returns the starting position of the occurrence of the specified substring in a specified string. If not found, 0 is returned. This method has two parameters:

Parameter 1: Specifies the string to be searched.

Parameter 2: Specifies the string used for retrieval.

Select charindex ('A', '123a123 ') -- returns 4 select charindex ('abc', '123a123') -- returns 0 select charindex ('abc', '123abc123 ') -- 4 is returned.

Stuff (): Used to delete characters of the specified length and insert new characters/values at the deleted position. This method has four parameters:

Parameter 1: Specifies the string to be operated.

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

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

Parameter 4: Specifies the New String/value inserted at the deleted position.

Select stuff ('123abc456 ', 'abc') -- returns 123ABC456select stuff ('123abc456', '') -- returns abc456, replacing it with an empty string

Len (): returns the length of the specified text value. Leading spaces are included, while trailing spaces are not included. This method has a parameter:

Parameter 1: Specifies the text or string to be operated.

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

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

The returned values range from 0 to 4. 0 indicates that the values are almost different or completely different. 4 indicates that the values are almost identical or identical. This method has two parameters:

Parameter 1: Specifies the SOUNDEX value of the first string to be compared.

Parameter 2: Specify the SOUNDEX value of the second string to be compared.

Select difference ('action', 'Demo') -- returns 2 select difference ('123', 'integer ') -- returns 4

Soundex (): return the SOUNDEX value of the specified string. SOUNDEX is a speech algorithm that uses English pronunciation to calculate an approximate value. The value consists of four characters. The first character is an English letter, and the last three are digits. In pinyin text, you may sometimes want to read but cannot spell out the correct words. You can use Soundex to perform fuzzy matching. Fuzzy match here is different from LIKE.

Brief Algorithm Description:

-- Replace English text with the following rules (do not match the first character, and do not use the value of an English character whose corresponding value is 0) a 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 m n -> 5 r-> 6

If two or more letters with the same numbers exist in the string (for example, j and k), delete the other strings and retain only one. Remove the character whose corresponding value is 0. Only the first four bytes are returned. If the value is not enough, enter 0.

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

PS: All characters except English characters will return 0000, so the second example of the Difference () method above will return 4 (representing identical ).

Lower (): return the lowercase string of the specified English string. If it is not an English string, the original value is returned. This method has a parameter:

Parameter 1: Specifies the string to be converted to lowercase.

Select lower ('abc') -- returns abcselect lower ('20140901') -- returns 123

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

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

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

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

Parameter 1: Specifies the string to delete leading spaces.

Select ltrim ('20140901') -- returns 123 select ltrim ('many spaces') -- returns a lot of spaces select len ('20140901 ') -- returns 11 select len (ltrim ('20140901') -- returns 3

Rtrim (): returns the string after trailing spaces are truncated. This method has a parameter:

Parameter 1: Specifies the string to be truncated and trailing spaces.

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

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

Parameter 1: Specifies the string to be operated, that is, the matched string.

Parameter 2: Specifies the string to be matched.

Parameter 3: Specifies a string used to replace an existing match.

-- Replace abc with xxx select replace ('123abc456 ', 'abc', 'xxx') -- returns 123xxx456 -- replace the matching item with an empty string: select replace ('123abc456 ', 'abc', '') -- returns 123456

The above is a summary of the commonly used SQL Server functions described in the editor. I hope it will help you. If you have any questions, please leave a message. The editor will reply to you in time, thank you very much for your support for the help House website!