List of commonly used SQL functions page 1/2

If you are a friend of sqlserver, you can refer to these functions to conveniently process the characters of fields.

If you are a friend of sqlserver, you can refer to these functions to conveniently process the characters of fields.

In a select statement, only SQL functions can be used to operate fields (link to SQL server ),
Select Field 1 from table 1 where field 1. IndexOf ("Cloud") = 1;
This statement is incorrect because the indexof () function is not an SQL function. You can change it to the corresponding SQL function.
Left () is an SQL function.
Select Field 1 from table 1 where charindex ('cloud', Field 1) = 1;

String functions perform different operations on binary data, strings, and expressions. This type of function applies to CHAR, VARCHAR, BINARY, and VARBINARY data types, and data types that can be implicitly converted to CHAR or VARCHAR. You can use string functions in the SELECT and WHERE clauses and expressions of SELECT statements.
Common string functions include:

I. character conversion functions
1. ASCII ()
Returns the ASCII value of the leftmost character of a character expression. In the ASCII () function, strings with numbers are not enclosed by '', But strings with other characters must be enclosed by''. Otherwise, an error occurs.
2. CHAR ()
Converts an ASCII code to a character. If no value is entered ~ ASCII code value between 255, CHAR () returns NULL.
3. LOWER () and UPPER ()
LOWER () converts all strings to lowercase letters, and UPPER () converts all strings to uppercase letters.
4. STR ()
Converts numeric data to numeric data.
STR ( [, Length [, ])
Length indicates the length of the returned string, and decimal indicates the number of decimal places returned. If no length is specified, the default length value is 10 and the default value of decimal is 0.
If length or decimal is a negative value, NULL is returned;
When length is less than the number of digits to the left of the decimal point (including the symbol bit), return length *;
Obey length first, and then take decimal;
When the number of digits of the returned string is less than length, spaces are filled on the left.
Ii. Space-removing Functions
1. LTRIM () removes spaces in the string header.

2. RTRIM () removes spaces at the end of the string.

Iii. substring Functions
1. left ()
LEFT ( , )
Returns character_expression, which starts from integer_expression at the left.

2. RIGHT ()
RIGHT ( , )
Returns character_expression, which is a string of characters starting from integer_expression.

3. SUBSTRING ()
SUBSTRING ( , , Length)
Returns the length of the string starting from the start _ position Character on the left.

Iv. String comparison functions
1. CHARINDEX ()
Returns the starting position of a specified substring in a string.
CHARINDEX (<'substring _ expression'>, )
Here, substring _ expression is the character expression to be searched, and expression can be a string or a column name expression. If no substring is found, the return value is 0.
This function cannot be used for TEXT and IMAGE data types.
2. PATINDEX ()
Returns the starting position of a specified substring in a string.
PATINDEX (<'% substring _ expression %'>, ) The neutron string expression must have the percent sign "%" before and after it; otherwise, the return value is 0.
Unlike the CHARINDEX function, wildcards can be used in the child strings of the PATINDEX function, which can be used for CHAR, VARCHAR, and TEXT data types.

V. String operation functions
1. QUOTENAME ()
Returns a string enclosed by a specific character.
QUOTENAME (<'character _ expression'> [, quote _ character]). quote _ character indicates the character used to enclose the string. The default value is "[]".
2. REPLICATE ()
Returns a character string that repeats character_expression for a specified number of times.
REPLICATE (character_expression integer_expression) if the value of integer_expression is negative, NULL is returned.

3. REVERSE ()
Sorts the characters of a specified string in reverse order.
REVERSE ( ) Character_expression can be a string, a constant, or a column value.

4. REPLACE ()
Returns the string with the specified substring replaced.
REPLACE ( , , ) Use string_expression3 to replace string_expression2 in string_expression1.

4. SPACE ()
Returns a blank string with a specified length.
SPACE ( ) If the value of integer_expression is negative, NULL is returned.

5. STUFF ()
Replace the specified position and length of the string with another substring.
STUFF ( , , , )
If the start position is negative or the length value is negative, or the start position is greater than the length of character_expression1, the return value is NULL.
If the length is greater than the right length of start _ position in character_expression1, character_expression1 retains only the first character.
Vi. Data type conversion functions
1. CAST ()
CAST ( AS [Length])

2. CONVERT ()
CONVERT ( [Length], [, Style])

1) data_type is the data type defined by the SQL Server system. Custom Data Types cannot be used here.
2) length is used to specify the data length. The default value is 30.
3) convert the CHAR or VARCHAR type to an INTEGER type such as INT or SAMLLINT. The result must be a value with a positive or negative number.
4) The conversion from TEXT to CHAR or VARCHAR is a maximum of 8000 characters, that is, the CHAR or VARCHAR data type is of the maximum length.
5) data stored in the IMAGE type is converted to the BINARY or VARBINARY type, up to 8000 characters.
6) convert the integer to the MONEY or SMALLMONEY type and process it according to the currency unit of the defined country, such as RMB, USD, and pound.
7) the BIT type conversion converts a non-zero value to 1 and is still stored as a BIT.
8) if you try to convert the data type to different lengths, the converted value will be truncated and "+" will be displayed after the converted value to identify the occurrence of this truncation.
9) use the style option of the CONVERT () function to display the date and time in different formats. Style is the conversion style number provided by the SQL Server system when converting DATATIME and SMALLDATETIME to a string. Different style numbers have different output formats.
VII. Date Functions
1. day (date_expression)
Returns the date value in date_expression.

2. month (date_expression)
Returns the month value in date_expression.

3. year (date_expression)
Returns the Year Value in date_expression.

4. DATEADD ()
DATEADD ( , , )
Returns the new date generated by the specified date plus the specified additional date interval number.
5. DATEDIFF ()
DATEDIFF ( , , )
Returns the difference between two specified dates in datepart, that is, the difference between date2 and date1. The result value is an integer with positive and negative signs.

6. DATENAME ()
DATENAME ( , )
Returns the specified part of the date in the form of a string. Specified by datepart.

7. DATEPART ()
DATEPART ( , )
Returns the specified part of the date in the form of an integer. This part is specified by datepart.
DATEPART (dd, date) is equivalent to DAY (date)
DATEPART (mm, date) is equivalent to MONTH (date)
DATEPART (yy, date) is equivalent to YEAR (date)

8. GETDATE ()
Return the current date and time of the system in the default format of DATETIME.

Internal Aggregate Function
1) COUNT (*) returns the number of rows
2) COUNT (distinct colname) returns the number of unique values in the specified Column
3) SUM (COLNAME/EXPRESSION) returns the value and of the specified column or EXPRESSION;
4) SUM (distinct colname) returns the SUM of unique values in the specified Column
5) AVG (COLNAME/EXPRESSION) returns the average value of a specified column or EXPRESSION.
6) AVG (distinct colname) returns the average value of the unique value in the specified Column
7) MIN (COLNAME/EXPRESSION) returns the minimum value of a specified column or EXPRESSION.
8) MAX (COLNAME/EXPRESSION) returns the maximum value of a specified column or EXPRESSION.