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

Source: Internet
Author: User

 

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.