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

This article introduces the knowledge of "what are the string functions commonly used in SQL Server". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. ASCII (string expression)

Function: returns the ASCII code value of the leftmost string of the expression.

Example: SELECT ASCII ('ABC'), ASCII (' A')-the result is 65 and the ASCII code value of the first string is calculated.

2. CHAR (integer value)

Purpose: converts ASCII code values of integer numeric types to characters. The integer value is an integer between 0 and 255.

Example: SELECT CHAR (57), CHAR (69)-return result 9 E

3. CHARINDEX (eg1,eg2,startindex)

Function: returns the starting position of the specified expression in the string

Parameter description:

Eg1: the string to find

Eg2: is the expression that contains the string to find

Startindex: specifies the starting position when searching for eg2 in eg1. If this parameter is not specified or if 0 or negative numbers are specified, the search starts from the first string by default. The position of the returned string was retrieved, but no 0 was returned.

Example: SELECT CHARINDEX ('Science', 'computer Information Science and Technology', 0)-returns result 6

4. DIFFERENCE (char_eg,char_eg)

Function: returns an integer value between 0,4, indicating the difference between the two string expression SOYNDEX values. 0 means almost or completely different. 4 means almost the same or exactly the same.

Parameter description:

Char_eg: an expression of type char or varchar, or type text, but the length is only valid for the first 8000 bytes.

Example: SELECT DIFFERENCE ('axiomageny AB'), DIFFERENCE (' axiom dagger 0'), DIFFERENCE ('apocryphal dagger')

-- output result 3 0 4

5. LEFT/RIGHT (eg,int_eg)

Function: intercepts a character of the specified length from the left / right side of the string and returns.

Parameter description:

Eg: can be any data type that can be implicitly converted to varchar or nvarchar.

Int_eg: the length of characters to be intercepted. Must be an integer greater than or equal to 0. A value equal to 0 represents an empty string.

Example: SELECT Right ('ABC',2), LEFT (' ABC',1)-- output result BC A

6. LEN (str_eg) calculates the string length

Purpose: returns the length of a string expression without trailing spaces.

Example: SELECT LEN (123), LEN ('123'), LEN (' 123')-return result 3 4 3

7. LOWWER/UPPER (str_eg) case conversion function

Function: to realize the case conversion of the string

Example: SELECT LOWER ('ABcDe'), UPPER (' ABcDe')-- output result abcde ABCDE

8. LTRIM/RTRIM (str_eg) removes the left / right space of the string expression

Example: SELECT LTRIM ('abc'), RTRIM ('abc')-- output result abc abc

9. NCHAR (int_eg)

Function: returns the Unicode character of the specified integer code as defined by the Unicode standard. Int_eg is a positive integer between 0,65535.

Example: SELECT NCHAR (68)-- output result D

10. PATINDEX ("% pattern", eg)

Function: returns the starting position of the first occurrence of a pattern in the expression, returns 0 if it is not found.

Parameter description:

Pattern: a text string that can use wildcards and must be followed by a% character before and after pattern (except for retrieving the first or last character)

Eg: is the string data type expression in which to find the pattern schema

Example: SELECT PATINDEX ('% Industrial%', 'Suzhou Industrial Park')-output result 3

11. QUOTENAME (eg1,eg2)

Function: returns a Unicode string with a delimiter

Parameter description:

Eg1: a string made up of Unicode strings.

Eg2: a single string of delimiters. Can be single quotation marks, double quotation marks, square brackets, parentheses, and so on. The default is square brackets.

Example: SELECT QUOTENAME ('abc',' ('), QUOTENAME ('abc')-- output result (abc) [abc]

12. REPLACE (eg1,eg2,eg3) string replacement function

Purpose: replace all eg2 in eg1 with eg3.

Example: SELECT REPLACE ('abcdefade','e','f')-- output result abcdffadf

13. REPLICATE (eg1,int_eg) repetition expression specified number of times

Parameter description:

Eg1: string expression

Int_eg: specify the number of times to repeat

Example: SELECT REPLICATE ('Beijing', 3)-- output result Beijing

14. REVERSE (eg) string reverse conversion

Function: reverse conversion of a string

Example: SELECT REVERSE ('12345')-output result 54321

15. The number of repeated spaces in SPACE (int_eg)

Example: SELECT 15, SPACE (5)

16. STUFF (char_eg1,startindex,length,char_eg2)

Purpose: deletes characters of the specified length and inserts another set of characters at the specified location.

Parameter description:

Char_eg1: string expression

Startindex: specify where to start the deletion or insertion

Length: specifies the number of characters to delete

Char_eg2: string expression to insert

Example: SELECT STUFF ('12345, 2, 3, 000)-- output result 10005

17. SUBSTRING (eg,start,length) string interception

Parameter description:

Eg: string expression

Start: an integer that specifies where to start intercepting.

Length: specifies the length to intercept

Example: SELECT SUBSTRING ('12345 June 2, 3)-- output result 234

This is the end of the content of "what are the string functions commonly used in SQL Server". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.