Special SQL Functions

1. coalesce () function

--- Introduction ---
The coalesce () function of the T-SQL allows you to specify multiple expressions, and coalesce () returns the first non-null value in the group. Use the following format, where expression can be any data type and can be equal to most legal expressions:

Case
When (expression1 is not null) Then expression1
...
When (expressionn is not null) Then expressionn
Else null

Coalesce returns NULL if all arguments (expression1-N) are null.

--- Example ---
Create Database dbtest
Use dbtest
Create Table basic
(
[ID] int identity (1, 1) primary key,
[Length] decimal null,
[Height] decimal null,
[Width] decimal null
)

Insert a data entry:
Insert into basic values (null, null, null)
Query statement:
Select coalesce (length, height, width) from basic

-- Query Result: NULL

Insert the following data:
Insert into basic values (1.00, null, null)
Insert into basic values (2.00, null, null)
Insert into basic values (3.00, null, null)
Insert into basic values (null, 4.00, null)
Insert into basic values (null, 5.00, null)
Insert into basic values (null, 6.00, null)
Insert into basic values (null, null, 7.00)
Insert into basic values (null, null, 8.00)
Insert into basic values (null, null, 9.00)

Query statement:
★Select coalesce (max (height), 0) + 1.00 from basic
★Select cast (coalesce (length * 2, height, width * 3) as decimal) as 'total list' from basic

Query results:
★7.00
★2, 4, 6, 4, 5, 6, 21, 24, and 27;
/* We usually use it to retrieve the index (sequential number, level number), consumer ID */

Returns the number of bytes occupied by any expression.
2. datalength

It is particularly useful for varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

The result of null datalength is null.

--- Example ---

In the preceding example, the query statement is as follows:
Select datalength (width) from basic

Query results: NULL, null, 5, 5, and 5 (that is, the decimal expression occupies 5 bytes and the int value is 4, you can try other SQL data types)
/* We generally use it to query the image and varbinary data types */

3.

The two functions nullif and isnull in SQL Server are described as follows:

Nullif (): two parameters are required.

Example: nullif (A, B)
Note: If a and B are equal, null is returned. If not, A is returned.
Select nullif ('eqeqweqw', '1'). The result is eqeqweqwe.
Select nullif () returns NULL
The types of A and B must be consistent.

Isnull (): two parameters are required.

Example: isnull (A, B)
NOTE: If both A and B are null, null is returned. If a is null and B is not null, B is returned. If a is not null and B is null, A is returned, if neither a nor B is null, A is returned.
Select isnull (null, null) returns NULL
The result of select isnull (null, 33) is 33.
Select isnull ('ddd ', null) results in DDD
Select isnull () result is 44

 

4.

Quotename (TRANSACT-SQL)

This function returns a unicode string with a separator added. This function changes the input string to a valid separator separated by Microsoft SQL Server.

 

Format:

Quotename ('character _ string' [, 'quote _ character '])

 

Parameters:

' Character_string '

This is a string consisting of Unicode characters.Character_stringIsSysnameThe maximum value is 128 characters. If you enter more than 128 characters, null is returned.

' Quote_character '

This is a single character string, which is assigned as a separator. It can make single quotes ('), Left or right brackets ([ ]), Or double quotation marks ("). IfQuote_characterIf not specified, square brackets are used by default.

Example:

select QUOTENAME('%' + 'awpatp'+ '%','''')

 

Result:
'% Yunzhang %'

select QUOTENAME( 'awpatp','[')

 

Result:
[Yunzhang]