advanced sql functions

By | July 23, 2019

Advanced SQL functions:

COALESE:

This function is used to return first non-NULL value from the list or from the table.

Examples:
SELECT COALESCE(NULL, 1, 2, ‘selenium99.com’); –> Returns 1
SELECT COALESCE(NULL, 500, NULL, 200, ‘selenium99.com’); –> Returns 500
SELECT COALESCE(NULL, 77, ‘automation’, ‘selenium99.com’); –> returns 77
SELECT COALESCE(NULL, NULL, ‘automation’, ‘selenium99’); –> Returns automation
SELECT COALESCE (NULL,NULL,NULL,NULL,’selenium99′,700); –> Return below error.
Error: Conversion failed when converting the varchar value ‘selenium99’ to data type int.

Learn below operations with NULL:
7+NULL returns NULL only.
7*NULL returns NULL
TRUE and NULL return NULL but
TRUE or NULL returns TRUE.

CAST:

CAST function is used to convert one datatype into a specified datatype.

Examples:
CAST(100.1 AS varchar);
Result: 100.1 (varchar datatype)

CAST(100.1 AS varchar);
Result: 100 (int datatype)

Leave a Reply

Your email address will not be published. Required fields are marked *