advanced sql functions

By | January 9, 2020

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)

 

Recommended Posts:

SQL Date Functions

SQL quick reference for interview preparation

Java program to split string

Maps in Java

Java interview questions

Hashset and LinkedHashset differences in java

Common exceptions in Selenium Webdriver

Leave a Reply

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