RSS

String Function in SQL Server 2012

14 Jan

CONCAT() Function

Concat Function is new function in SQL Server 2012 this Function is very Great Function becouse in 2008 when we want to concat two name + Number we must make ++and Cast the Number to String that’s very hard today in this version we will make this operation in one and easy step . Let’s See How :

SELECT CONCAT(1, 2, 3, 4) AS SingleString
SELECT CONCAT(‘One’,1, 1.1, GETDATE()) AS SingleString
SELECT CONCAT(‘One’,2,NULL) AS SingleString
SELECT CONCAT(”,”,”,”) AS SingleString
SELECT CONCAT(NULL, NULL) AS SingleString

in old SQL Server when you want to conact Mostafa + Elmasry +1985 you write

Select ‘Mostafa’ + ‘ Elmasry’ + Cast(‘ 1985’ as Nvarchar(10)) AS My_Name

But in New Version that’s very easy see :

Select Concat(‘Mostafa’+’ Elmasry’+1985)

 

 

Format() Function

SET DATEFORMAT DMY
GO
DECLARE @DAY DATETIME = ’17/09/2010′;
SELECT FORMAT ( @DAY, ‘d’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @DAY, ‘d’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @DAY, ‘d’, ‘de-DE’ ) AS DE_Result;
GO
DECLARE @Month DATETIME = ’17/09/2010′;
SELECT FORMAT ( @Month, ‘M’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @Month, ‘M’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @Month, ‘M’, ‘de-DE’ ) AS DE_Result;
GO
DECLARE @YEAR DATETIME = ’17/09/2010′;
SELECT FORMAT ( @YEAR, ‘Y’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @YEAR, ‘Y’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @YEAR, ‘Y’, ‘de-DE’ ) AS DE_Result;
GO

Day
SELECT FORMAT ( GETDATE(), ‘d’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( GETDATE(), ‘dd’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( GETDATE(), ‘ddd’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( GETDATE(), ‘dddd’, ‘en-US’ ) AS US_Result;

Month
SELECT FORMAT ( GETDATE(), 'm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmmm', 'en-US' ) AS US_Result;

Year
SELECT FORMAT ( GETDATE(), 'y', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yy', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yyy', 'en-US' ) AS US_Result;

Currency
DECLARE @var INT = 50
SELECT FORMAT(@var,’c’) AS Currency;
SELECT FORMAT(@var,’c1′) AS Currency;
SELECT FORMAT(@var,’c2′) AS Currency;
SELECT FORMAT(@var,’c3′) AS Currency;
GO
DECLARE @d INT = 500;
SELECT FORMAT ( @d, ‘c’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @d, ‘c’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @d, ‘c’, ‘de-DE’ ) AS DE_Result;

miscalculation format
DECLARE @var INT = 50
SELECT FORMAT(@var,’p’) AS Percentage;
SELECT FORMAT(@var,’e’) AS Scientific;
SELECT FORMAT(@var,’x’) AS Hexa;
SELECT FORMAT(@var,’x4′) AS Hexa1;

language
SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’, ‘en-US’) AS English_Result;
SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’, ‘AR’) AS ARABIC_Result;
SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’, ‘gu’) AS Gujarati_Result;

 
Leave a comment

Posted by on January 14, 2012 in New Function

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s