SQL Server Advanced Functions

Main Article :- Sql Server CAST() Function

The CAST in is used to transform a value (of any type) or an expression from one type to another.

The returns an error if the conversion fails. If not, the converted value will be returned.

Syntax:

The CAST method has the following syntax:

CAST(expr AS target_type(length))

Parameter Values

Technical Details

Examples:-

Let's have a look at some CAST demonstrations and see how to use it.

Example 1: This example demonstrates how to convert a decimal type to the varchar datatype:

SELECT CAST(18.75 AS varchar) as CastingVarchar;

Result:

The result converts 18.75 into varchar data type 18.75.

Example 2: This example demonstrates how to convert a string type to the datetime datatype:

SELECT CAST('2022-05-16' AS datetime) AS CastingDateTime;

Result:

The string '2022-05-16' is converted to a datetime in this output using the CAST .

Example 3: This example demonstrates how to convert a decimal type to the int datatype:

SELECT CAST(18.75 AS int) as CastingInt;

Result:

The result converts 18.75 into int data type 18.

Main Article :- Sql Server COALESCE() Function

The COALESCE expression takes a list of parameters, analyzes them in order, and returns the first non-null argument.

Syntax:

The COALESCE method has the following syntax:

COALESCE(value1, value2, ...., value_n)

Parameter Values

Technical Details

Examples:-

Example 1: Return the first non-null value in a list is used in this example:

SELECT COALESCE(NULL, NULL, 'Simmanchith.com', NULL, 'JpLearn.com');

Result:

The coalesce was used to return the first non-null value in the list, "Simmanchith.com".

Example 2: Return the first non-null value in a numeric data list is used in this example:

SELECT COALESCE(NULL, NULL, 400, 250);

Result:

This result evaluates a list of parameters and returns the first integer using the COALESCE expression.

The CONVERT transforms an expression or value (of any type) into a different datatype.

If the conversion fails, the will raise an error. The converted value will be returned if this is not the case.

Tip: The CONVERT is similar to the CAST .

Syntax:

The CONVERT method has the following syntax:

CONVERT(target_type(length), expr, style)

Parameter Values

Technical Details

Examples:-

Example 1: This example shows how to change the data type of an expression (varchar):

SELECT CONVERT(varchar, 18.75);

Result:

The decimal datatype is transformed to varchar datatype 18.75 as a result of this.

Example 2: This example shows how to change the data type of an expression (datetime):

SELECT CONVERT(datetime, '2022-05-16');

Result:

The string '2022-05-16' is converted to a datetime in this output using the CONVERT .

Example 3: The example illustrates a Convertion of an expression (varchar) from one data type to another:

SELECT CONVERT(varchar, '2022-05-16', 101);

Result:

Through the result, the converts string types into varchar with 101 formats within it.

Example 4: The example illustrates a Convertion of an expression (int) from one data type to another:

SELECT CONVERT(int, 18.75);

Result:

Through the result, the CONVERT is used to convert the decimal number 18.75 to an integer 18.

The name of the current user in the database is returned by the CURRENT_USER .

Features:

• The current user's name is found using this .
• There are no parameters for this .

Syntax:

The CURRENT_USER method has the following syntax:

CURRENT_USER

Technical Details

Examples:-

Example 1: Getting the name of the current user with the CURRENT_USER :

SELECT CURRENT_USER;

Result:

As you can see from the output, this returns the current user.

The IIF method returns one value if the condition evaluates To true, and another value if the criterion is FALSE.

To add if-else logic to queries, utilise the IIF .

Syntax:

The IIF method has the following syntax:

IIF(boolean_condition, value_if_true, value_if_false)

Parameter Values

Technical Details

Examples:-

Example 1: Check out this example. If the condition is TRUE, return 1; if the condition is FALSE, return 2:

SELECT IIF(150<1500, 1, 2);

Result:

This result produces the True string 1 and the IIF to check if 150 < 1500.

Example 2: Check out this example. If salary is greater than 20000, return 'MORE SALARY'; if the condition is not satisfied, return 'LESS SALARY':

Let's take "EMPLOYEESIMPLE" table to execute this example:

The query will be look like this,

SELECT id, dept, salary,
IIF(Salary>20000, 'MORE SALARY', 'LESS SALARY') as STATUS
FROM EMPLOYEESIMPLE;

Result:

The salary column in the result verified whether the employee's salary is greater than 20,000. Because several conditions are met, the result is "MORE SALARY". Otherwise, "LESS SALARY" is returned.

Main Article :- Sql Server ISNULL() Function

The ISNULL in substitutes a value for NULL.

If the expression is not NULL, this method returns the expression.

Syntax:

The ISNULL method has the following syntax:

ISNULL(expr, replace_value)

Parameter Values

Technical Details

Examples:-

Example 1: Let's look at an example to explore how to use the ISNULL :

SELECT ISNULL('Welcome', 'Simmanchith.com');

Result:

Because the first parameter is not NULL, the result utilises the ISNULL to return the string 'Welcome'.

Example 2: The provided value will be returned. If the expression is NULL, return it. otherwise what happen let's see:

SELECT ISNULL(NULL, 'Simmanchith.com') AS StringISNull,
ISNULL(NULL, 50) AS NumberISNull;

Result:

The returns the second parameter because the first is null.

ISNUMERIC determines whether or not an expression is numeric.

When an expression in is a valid number, the ISNUMERIC returns 1. It returns 0 if not.

Syntax:

The ISNUMERIC method has the following syntax:

ISNUMERIC(numeric_value)

Parameter Values

Technical Details

Examples:-

Example 1: In this example, let's compare the isnull inside numeric number:

SELECT ISNUMERIC(4321) as PositiveNumber,
ISNUMERIC('4321') as WithQuotes,
ISNUMERIC(5 * 10) as NumericExpression,
ISNUMERIC(-1234) as NegativeNumber;

Result:

From the result, we used a numeric value in this , so it returns 1.

Example 2: In this example, let's use non numeric values:

SELECT ISNUMERIC('Simmanchith.com') as Character, 
ISNUMERIC(NULL) as NullValue,
ISNUMERIC('2022-05-16') as DateValue;

Result:

From the result, we used a non numeric value in this , so it returns 0.

In MySQL NULLIF , If both parameters are equal, the NULLIF expression returns NULL. If not, the first expression is returned.

Syntax:

The NULLIF method has the following syntax:

NULLIF(expression1, expression2)

Parameter Values

Technical Details

Examples:-

Example 1: This example to compare two same expressions:

SELECT NULLIF('Welcome', 'Welcome') as StringCompare,
NULLIF(250, 250) as NumberCompare,
NULLIF('2022-05-16', '2022-05-16') as DateCompare;

Result:

Because the first and second arguments are equal, this output returns NULL.

Example 2: This example to compare two different expressions:

SELECT NULLIF('Welcome', 'Hello') as StringCompare,
NULLIF(150, 250) as NumberCompare,
NULLIF('2021-05-22', '2022-11-29') as DateCompare;

Result:

The above example uses the NULLIF expression. It returns first character because the first character string is not equal to the second one.

The SESSION_USER in retrieves the current session's user name.

Syntax:

The SESSION_USER method has the following syntax:

SESSION_USER

Technical Details

Examples:-

Example: This example to return the current user's user name:

SELECT SESSION_USER;

Result:

The result shows that current user name scsqlserver19.

SESSIONPROPERTY returns the session properties for a given option.

Syntax:

The SESSIONPROPERTY method has the following syntax:

SESSIONPROPERTY(choice)

Parameter Values

Technical Details

Examples:-

Example 1: In this case, Return the session settings for a given option:

SELECT SESSIONPROPERTY('ANSI_NULLS') as ANSI_NULL,
SESSIONPROPERTY('ANSI_PADDING') as ANSI_PADDING,
SESSIONPROPERTY('ANSI_WARNINGS') as ANSI_WARNINGS,
SESSIONPROPERTY('ARITHABORT') as ARITHABORT;

Result:

From the result, it will give you 1 if it's ON. otherwise it will return 0 if its OFF.

Example 2: Let's check other properies also:

SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') as CONCAT_NULL_YIELDS_NULL,
SESSIONPROPERTY('NUMERIC_ROUNDABORT') as NUMERIC_ROUNDABORT,
SESSIONPROPERTY('QUOTED_IDENTIFIER') as 'QUOTED_IDENTIFIER;

Result:

From the result, it will give you 1 if it's ON. otherwise it will return 0 if its OFF.

The database's SYSTEM_USER returns the current user's login name information.

Syntax:

The SYSTEM_USER method has the following syntax:

SYSTEM_USER

Technical Details

Examples:-

Example: Example to teturn the current user's login name information:

SELECT SYSTEM_USER;

Result:

Through the example we used to return current user name.

The USER_NAME method returns the database user name based on the given id.

If no id is given, this method returns the current user's name.

Syntax:

The USER_NAME method has the following syntax:

USER_NAME(identification_number)

Parameter Values

Technical Details

Examples:-

Example 1: The database user name with id 1 is returned in this example:

SELECT USER_NAME(1);

Result:

From the result, it will display your first user name.

Example 2: The database user name without id is returned in this example:

SELECT USER_NAME;

Result:

Through the result, it will display your current user name(if you are not specified id).