SQL coalesce () function, datalength () function introduction and application

 

1. Common SQL date format conversion methods

Posted by Ouyang Zhenhua on 8:46:20

1. Select CONVERT (varchar (12), getdate (), 101)
2. 09/12/2004
3.  
4. Select CONVERT (varchar (12), getdate (), 103)
5. 12/09/2004
6.  
7. Select CONVERT (varchar (12), getdate (), 104)
8. 12.09.2004
9.  
10. Select CONVERT (varchar (12), getdate (), 105)
11. 12-09-
12.  
13. Select CONVERT (varchar (12), getdate (), 106)
14. 12 09 2004
15.  
16. Select CONVERT (varchar (12), getdate (), 107)
17. 09 12,200 4
18.  
19. Select CONVERT (varchar (12), getdate (), 108)
20. 11:06:08
21.  
22. Select CONVERT (varchar (12), getdate (), 109)
23. 09 12, 2004 1

1. Select CONVERT (varchar (12), getdate (), 110)
2. 09-12-2004
3. Select CONVERT (varchar (12), getdate (), 113)
4. 12 09 2004 1
5.  
6. Select CONVERT (varchar (12), getdate (), 114)
7. 11:06:08. 177

1. COALESCE () function

--- Introduction ---
Of T-SQLCOALESCE ()Function allows you to specify multiple expressions, andCOALESCE ()The first non-empty value in the group is returned. Use the following format, whereExpressionIt can be any data type and can be equal to most legal expressions:
COALESCE (Expression[,...N]) -- (Equivalent to the CASE function)

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 */

2. DATALENGTH () function

--- Introduction ---

Returns the number of bytes occupied by any expression.
DATALENGTH 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 */