Database series queries (2)

String functions

1 LEN Returns the number of characters (not bytes) of the specified string, which does not contain trailing spaces

SELECT LEN (' Li Lizhan author '-- return: 5

2 UPPER convert lowercase characters to uppercase characters

SELECT UPPER ('Book Books table '-- return: Book Books table

3 LTRIM Returns a string that removes the left space

SELECT LTRIM ('  Authors'-- return: Authors

4 CHARINDEX to find the starting position of a specified string in another string

SELECT CHARINDEX ('L'HELLO'1-- return: 3 

5 Left returns the specified number of characters in a string

SELECT  Left ('zhangsan'2-- return: ZH

6 Datalength Returns the number of bytes in the specified string

SELECT datalength (' Chinese '--- return: 6

7 Replace replaces a character in a string

SELECT REPLACE (' I love my    hometown ' home ' ' school  '  -- back: I love my school.

8 Sbustring The specified number of substrings from the starting position of the string strings

SELECT SUBSTRING (' I love my hometown ',32-- return: My

Mathematical functions

1 ABS returns the absolute value of an expression

SELECT ABS (------ return:

2 ROUND rounding by specified precision

SELECT ROUND (56.6292-- return: 56.630

3 SQRT Returns the square root of the specified expression

SELECT SQRT (9-- return: 3

4 floor Returns the largest integer less than or equal to the specified numeric expression

SELECT  Floor (23.9-- return:

5 CEILING returns the smallest integer greater than or equal to the specified numeric expression

SELECT CEILING (23.9-- return:

Time function

1 GETDATE Get current system date

SELECT GETDATE  -- return: System Current date

2 DATEPART Returns the integer of the specified date part

' 01/09/2003 '  -- return: 9

3 DATEADD A new date by adding a value on the specified date

SELECT DATEADD 4 ' 01/09/2003 '  -- return: 2007-01-09

4 DATEDIFF Returns the difference of two dates

SELECT DATEDIFF ' 02/05/2003 ' ' 02/09/2005 '  -- return: 735

5 Datename Returns the string for the specified date part

SELECT Datename ' 02/02/2009 '  -- return: Monday

6 year returns the integer for the specified date "Years" section

SELECT  Year (GETDATE-- return: Current year integer

7 month returns the integer for the specified date "months" section

SELECT MONTH (GETDATE-- return: Current month integer

8 day Returns the integer for the specified date "date" section

SELECT  Day (GETDATE-- return: current date integer

Conversion functions

Convert data type conversion: Converts the 2nd parameter to the type specified by the first parameter.

SELECT CONVERT (DateTime, ' 2020-09-09 ')
return: 2020-09-09 00:00:00.000
SELECT CONVERT (varchar (one), GETDATE (), 121)
return: 2010-03-24
Common date formats:
121: Date format is YYYY-MM-DD hh:mi:ss.mmm
111: Date format is YY/MM/DD
SELECT CONVERT (varchar (5), 92.89)
Returns: 92.89

Aggregation functions

1. SUM

SELECT SUM  as  from Customers

2. AVG

SELECT AVG  as  from Customers

3, Max and Min

SELECT MAX  as MIN  as  from Customers -- Max and Min can also be used to calculate character types and date-time type data columns in addition to numeric columns SELECT MAX  as  from Customers

4, COUNT (expression)
Returns the number of non-empty rows in the result set.
where "expression" can be "*", "column name".
(1) Count (*): Returns the number of records for all rows of data in the table.
(2) COUNT (column name): Returns the number of non-null values for the specified column.
Example

SELECT COUNT (*as fromWHERE>SELECTCOUNTas   from Customers

Case function

Case has two formats. Simple case function and case search function.
1 Simple Case function

 Case Sex       when ' 1 '  Then ' male '      when ' 2 '  Then ' female ' Else ' other ' End

2 Case Search function

 Case  when = ' 1 '  Then ' male '      when = ' 2 '  Then ' female ' Else ' other ' End

Database series queries (2)