You are here

SQL:Logical Functions

Logical Functions

Logical Functions

In this post we will discuss logical functions like IIF, CHOOSE, ISNUMERIC. The output of these functions are determine by comparative logic. These functions simplify migrations from Microsoft access platforms and it is nonstandard T-SQL functions.

IIF()

  • IIF() returns one of two values, depending on whether the boolean expression evaluates to true or false.
  • IIF() function is like a ternary operator(?:).
  • Syntax:

IIF(boolean_expression, true_result, false_result)
Example:
Declare @a int=21, @b int=10;
select IIF(@a>@b, ‘True’, ‘False’) as Result;
output:

Result

True

CHOOSE()

  • CHOOSE() function is use to give a position and a list of expressions, and it returns expression in the given position.
  • Syntax:

CHOOSE(positions, exp1, exp2,…., expn)

Example:
select CHOOSE(2,’Jiya’,’Riya’,’Priya’) as Techwithr;
output:

Techwithr

Riya

ISNUMERIC()

  • ISNUMERIC() function determines whether an expression is a valid numeric type.
  • ISNUMERIC() returns 1 if given expression is valid numeric otherwise it returns 0.
  • Syntax:

ISNUMERIC(expression)
Example:
Declare @size int=21;
select ISNUMERIC(@size) as ans;
output:

ans

1

Example:

-create table information (info_id int NOT NULL, pro_name varchar (50) NULL, size int NULL, department varchar(50) NULL);
-insert into information (info_id, pro_name, size, department) values(1,’T-shirt’,30,’Fashion’),(2,’Jeans’,34,’Fashion’),(3,’phone’,5,’Mobile’),(4,’Top’,40,’Fashion’);
-select * from information;
output:

info_id

pro_name size department
1 T-shirt 30

Fashion

2

Jeans 34 Fashion
3 phone 5

Mobile

4

Top

40

Fashion

check whether the particular product is available in the store or not

  • select pro_name, IIF (info_id In(1,2,3,4),’Product is available’, ‘Product is not available’) as ISProduct from information;

output:

pro_name

ISProduct
T-shirt

Product is available

Jeans

Product is available
phone

Product is available

Top

Product is available

retrieve the department of product using CHOOSE()

  • select info_id,CHOOSE(info_id,’T-shirt’,’Jeans’,’phone’,’Top’)as Result from information;

output:

Info_id

Result
1

T-shirt

2

Jeans
3

phone

4

Top

check the product size is numeric or not.

  • select pro_name, size, ISNUMERIC(size)as sizenumeric from information;

output:

pro_name

size
T-shirt

1

Jeans

1
phone

1

Top

1

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.
You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top