New function in DENALI SQL SERVER 2012 (CHOOSE)

Hello friends,

This is another post related to new functions in DENALI (SQL SERVER 2012).

So today I am going to discuss “CHOOSE” functions .By the name it is clear that it will choose value.

In a simple sentence you can say it array which give value according to index parameter.

Its syntax is very simple.

CHOOSE (VALUE,CHOICE1, CHOICE2….CHOICEN)

Below is example.

 

In the example we are passing month value in CHOOSE function and according to value it provide corresponding string value of month.

 

DECLARE @tblFriends AS TABLE (Prefix VARCHAR(10) ,
Fname VARCHAR(200),
LName VARCHAR(100),
DOB SMALLDATETIME)

INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('DR','Ravindra','Verma','1/1/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Mr','Rajul','Verma','3/3/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Sir','Suyash','Kokje','4/4/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Sardar','Ravindra','Dhariwal','2/2/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Pandit','Shyam','Purohit','5/5/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Shrikant','Yadav','6/6/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Sawan','Gupta','7/7/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Pankaj','Tomar','8/8/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Vaibhav','Joshi','9/9/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Vikram','Chouhan','10/10/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Subhash','Patidar','11/11/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Vinay','Karma','12/12/1981')

SELECT CONCAT(Prefix,' ' ,FName, ' ',LName) AS ConcatName,
CHOOSE (datePart(m,DOB),'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC') AS DOBMONTH
FROM @tblFriends

so when you execute this script you will get following result.

I hope you enjoyed learning this.

ConcatName DOBMONTH
DR Ravindra Verma JAN
Mr Rajul Verma MAR
Sir Suyash Kokje APR
Sardar Ravindra Dhariwal FEB
Pandit Shyam Purohit MAY
Shrikant Yadav JUN
Sawan Gupta JUL
Pankaj Tomar AUG
Vaibhav Joshi SEP
Vikram Chouhan OCT
Subhash Patidar NOV
Vinay Karma DEC
You can view previous post related with this at

Thanks

Rajat Jaiswal

 

 

 

New Functions in SQL SERVER 2012 ,DENALI (CONCAT)

Hello friends,
This is another post related to new functions in DENALI (SQL SERVER 2012).
So today I am going to discuss “CONCAT” functions .By the name it is clear that it has concatenation property.

Its syntax is very simple.
CONCAT (string1, string2….StringN)
The best part is it handles NULL value also we don’t have to work on NULL value explicitly.
Below is example.
DECLARE @tblFriends AS TABLE (Prefix VARCHAR(10) ,
Fname VARCHAR(200),
LName VARCHAR(100),
DOB SMALLDATETIME)

INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘DR’,'Ravindra’,'Verma’,’1/1/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Mr’,'Rajul’,'Verma’,’3/3/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Sir’,'Suyash’,'Kokje’,’4/4/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Sardar’,'Ravindra’,'Dhariwal’,’2/2/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Pandit’,'Shyam’,'Purohit’,’5/5/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Shrikant’,'Yadav’,’6/6/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Sawan’,'Gupta’,’7/7/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Pankaj’,'Tomar’,’8/8/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Vaibhav’,'Joshi’,’9/9/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Vikram’,'Chouhan’,’10/10/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Subhash’,'Patidar’,’11/11/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Vinay’,'Karma’,’12/12/1981′)

SELECT CONCAT(Prefix,’ ‘ ,FName, ‘ ‘,LName) AS ConcatName
FROM @tblFriends

When you run the above script you will get following resultset.
 
ConcatName
DR Ravindra Verma
Mr Rajul Verma
Sir Suyash Kokje
Sardar Ravindra Dhariwal
Pandit Shyam Purohit
Shrikant Yadav
Sawan Gupta
Pankaj Tomar
Vaibhav Joshi
Vikram Chouhan
Subhash Patidar
Vinay Karma

I hope you enjoyed learning this.
You can view previous post related with this at http://indiandotnet.wordpress.com/2011/12/23/new-function-in-sql-server-denali-iif/


Thanks
Rajat Jaiswal