Common SQL server system functions-string functions (1): SQL Server strings

Common SQL server system functions-string functions (1): SQL Server strings

I haven't written a blog for a long time. During this time, I want to write a string function.

QQ group: 499092562; Welcome

String functions:

1. LEN (string to be obtained)

Returns the length of the string.

Example:

Select len ('little Porter! ')

2. RIGHT (the string to be intercepted from the RIGHT, the starting subscript to be intercepted, and the length to be intercepted)

Return: string on the right

Example:

Select right ('little Porter ', 2, 2)

The 'little docker' starts from the second subscript on the right and is a string of two characters.

3. CHARINDEX)

Example: select charindex ('small Porter ', 'job', 1)

Find the location where the 'hander' word in the 'small docker' is located from the first position.

4. LTRIM (string to be cleared );

Return Value: the string with spaces left to be cleared.

Example:

Select ltrim ('small Porter ')

5. RTRIM (the string on the right needs to be cleared)

Return: string with spaces on the right

6. STUFF (string, starting subscript, intercepting length, and inserting string)

In a string, delete the specified length of characters and insert a new string at this position;

Example

Select stuff ('abcdefg', 2, 3, '小 ')

Return: A small Porter EFG

7. REPLACE (specifies the string to be replaced, and the string to be replaced)

Returns a new string.

Example:

Select replace ('small Porter ', 'small', 'Big ')
Return: big porter

8,

UPPER (string to be converted to uppercase)

Converts the string passed to it to uppercase.

Select upper ('SQL server class ')

Back: SQL SERVER Course

9. SUBSTRING (string, start subscript, length)

Extract string

Example

Select substring ('small Porter ', 2, 3)

Return: Porter

10,Returns the reverse value of the string value.
REVERSE (string that requires REVERSE value)

Example:

Select reverse ('little Porter ')

Return to Work Migration

11,Repeat the string value with a specified number of times
REPLICATE (string to be repeated, repeated times)

Example:

Select replicate ('little Porter ', 3)

Return to the little Porter, the little Porter, and the little Porter.

12. returns the integer of the first character of the input expression.

UNICODE (the first integer string must be returned)

'Need to returnFirst characterInteger string'Is an nchar or nvarchar expression.

Example:

Select unicode ('小 ')

Return Value: 23567
Select unicode ('small Porter ')

Return Value: 23567

13,

Pronunciation matching degree
SOUNDEX () is used to calculate the pronunciation characteristics of a string,

Returns a four-character string,

And the first character of the returned value is always the first character in the initial string,

Then there is a three-digit number.

SELECT stuName, SOUNDEX (stuName) FROM Stu_Info

Result:

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 pronunciation feature value is very complex. If we want to analyze the pronunciation similarity between the two strings based on the two pronunciation feature values, it is difficult to use SOUNDEX.
DIFFERENCE () is a good choice. It can calculate the pronunciation feature values of two strings and compare them to simulate similarity.
Return a value ranging from 0 to 4 to reflect the pronunciation similarity between the two strings. The larger the value, the higher the pronunciation similarity between the two strings.

SELECT stuInfoq, SOUNDEX (stuName), DIFFERENCE (stuName, 'H') FROM Stu_Info:

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

I will try again next time, hey