SQL Server Other functions

This article is a record of the programmer's SQL code, and this time it will cover other functions commonly used by SQL Server. (Other database is not listed here, want to see more attention to the "Programmer's SQL Code").

Other functions include: type conversion functions, null value processing functions, Process Control functions, SQL Server unique functions.

function of type conversion

CAST (expression as data_type) function CONVERT (data_type, expression)

The above two functions are all functions provided by SQL Server that support type conversions. The parameter expression the expression to be converted (that is, the data that needs to be converted), and data_type is the target type of the transformation (that is, the type to be converted).

SELECT
CAST (' -30 ' as INTEGER) as I,
CONVERT (DECIMAL, ' 3.1415726 ') as D,
CONVERT (DATETIME, ' 2008-08-08 08:09:10 ') as DT

Functions for null-valued processing

COALESCE (Expression,value1,value2......,valuen) function: A function that handles a null-value problem and returns the first non-empty expression in all parameters, including expression. Where the expression is the expression to be detected, and the number of parameters after it is not fixed, can be multiple. Returns a expression if the expression is not NULL, or if the value1 is empty, returns value1 if it is not NULL, or if the value2 is empty, and returns value2 if it is not a null value. And so on, if the arguments in the COALESCE function are all null, there will be an error, because the function of the COALESCE function is to avoid unwanted null values.

SELECT Fname,fbirthday,fregday,
COALESCE (Fbirthday,fregday, ' 2008-08-08 ') as Importday
From T_person

ISNULL (expression,value) function: This function is also a function of NULL value processing, is a simplified version of the coalesce () function, only supports two parameters. Where expression is the expression to be detected, if expression is not NULL, return expression, or whether value is NULL, return value if not NULL, return NULL if all is null.

SELECT Fbirthday,fregday,
ISNULL (Fbirthday,fregday) as Importday
From T_person

Nullif (expression1, expression2) function: This function is also a null value processing function, mainly to determine whether two expressions are equivalent, if equivalent, then return NULL, if not equivalent, the side returns the first expression1 value. It should be noted that the first expression expression1 cannot be empty.

SELECT Fbirthday,fregday,
Nullif (Fbirthday,fregday)
From T_person

Process Control functions

SQL Server provides a process control function, similar to the switch in our code ... Case statement. That's the case function, which has the following two ways to use it.

The syntax for the case function is as follows:

Usage One:

Case expression then
value1 THEN returnvalue1 when
value2 THEN returnvalue2 when
value3 THEN returnvalue3
   ... ELSE Defaultreturnvalue End

The case function tests the expression expression and returns returnvalue1 if the expression equals value1, and returns expression if Value2 equals Returnvalue2. Expression equals Value3 returns RETURNVALUE3, ... And so on, if you do not meet all the when conditions, return the default value Defaultreturnvalue.

SELECT
FName,
(case FName when
' Tom ' THEN ' goodboy ' when
' Lily ' THEN ' goodgirl ' when
' Sam ' THEN ') BadBoy ' When
', ' Kerry ' THEN ' badgirl '
ELSE ' Normal ' End
, as Isgood from
T_person

Usage Two:

Case when
condition1 THEN returnvalue1 then
condition 2 THEN returnvalue2 when
condition 3 THEN Returnvalu E3 ...
ELSE Defaultreturnvalue End

One of the condition1, condition 2, Condition 3 ... As a conditional expression, the case function tests each expression backwards and returns returnvalue1 if the condition condition1 true, or returns returnvalue2 if the condition condition2 true. Otherwise, if the condition Condition3 is true, return returnvalue3 ... And so on, if you do not meet all the when conditions, return the default value Defaultreturnvalue.

SELECT
FName,
fweight,
(case if
fweight<40 THEN ' thin ' when
fweight>50 ' fat '
ELSE ' OK '
End) as Isnormal from
T_person