Summary of how to use common functions in SQL Server

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.

select substring('abcdef',1,3)  -- 返回 abc
select substring('123456321',0,2)  -- 返回 1，即第一位，最好不要这样做

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.

select LEFT('abc123',3)    -- 返回 abc
select LEFT('左侧部分右侧部分',4)  -- 返回 左侧部分

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.

select RIGHT('abc123',3)    -- 返回 123
select RIGHT('左侧部分右侧部分',4)  -- 返回 右侧部分

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.

select charindex('a','123a123')    -- 返回 4
select charindex('abc','123a123')  -- 返回 0
select charindex('abc','123abc123')  -- 返回 4

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.

select stuff('123abc456',4,3,'ABC')    -- 返回 123ABC456
select stuff('123abc456',1,3,'')  -- 返回 abc456，用空字符串替代

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.

select len('123')  -- 返回 3
select len('字符串')  -- 返回 3

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.

select difference('action','demo')    -- 返回 2
select difference('123456','整数')    -- 返回 4

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:

-- 将英文字按以下规则替换（不使用第一个字符进行匹配，并且不使用对应值为 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 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.

select soundex('string')  -- 返回 S215
select soundex('str')  -- 返回 S210
select soundex('123')  -- 返回 0000
select soundex('字符串')  -- 返回 0000

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.

select lower('ABC')    -- 返回 abc
select lower('123')  -- 返回 123

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.

select upper('abc')    -- 返回 ABC
select upper('123')  -- 返回 123

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.

select ltrim('    123')    -- 返回 123
select ltrim('    好多空格')  -- 返回 好多空格
select len('    123')  -- 返回 11
select len(ltrim('    123')) -- 返回 3

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.

select rtrim('123     ')    -- 返回 123
select len(rtrim('123     ')) -- 返回 3

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.

--把 abc 替换为 xxx 
select replace('123abc456','abc','xxx')    -- 返回 123xxx456
-- 用空字符串替换匹配项
select replace('123abc456','abc','')  -- 返回 123456

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!