Seven functions of SQL Study Notes: number, date, type conversion, null value processing, Case

Numeric Functions

ABS ():Returns the absolute value.

Ceiling ():Round to the maximum integer.

Floor ():Round to the smallest integer.

Round (): Rounding Round (a, B) Is the number to be processed, B The number of digits after the decimal point.

Len ():Calculate the string length

Lower (),Upper ():Lowercase or uppercase

Ltrim ():Remove spaces on the left of the string

Rtrim ():Remove spaces on the right of the string

Substring (string, start_position, length)String Is a string to be processed, Start_position Is the number of start interceptions, Length Is the maximum length of the clip.

 

Date Functions

Getdate ():Get current date and time

Dateadd (datepart, number, date), Calculate the date after the addition

Example: Dateadd (day, 3, date) Calculation date Date After 3 Day date

Dateadd (month,-8, date) Calculation date Date Before 8 Date of month

Datediff (datepart, startdate, enddate ):Calculate the difference between two dates.

Datepart (datepart, date ):Returns a specific part of a date.

 

Type conversion functions

Cast (expressionAsData_type)

Convert (data_type, expression)

 

Null Value processing function If the query result is null, it is the second parameter. If it is not null, it is the first parameter.

Select Isnull(Fname,' Region name ' ) As Name From T_employee

 

 

CaseFunction usage

Single value judgment, equivalentSwitch case

Case expression

When value1 then returnvalue1

When value2 then returnvalue2

When value3 then returnvalue3

Else defaultreturnvalue

End