Replicate Function in SQL Server 2012

This is a New function in SQL Server 2012

It repeats the string/character expression N number of times specified in the function.

Example :

DECLARE @FirstString nVARCHAR(MAX)
SET @FirstString = REPLICATE('A',11)
Select @FirstString
SELECT LEN(@FirstString) LenFirstString;

Result
AAAAAAAAAAA

LenFirstString
11

New Conversion Function In SQL Server 2012

Conversion Function

1- Parse()  function (can convert any string value to Numeric or Date/Time format.)

–Example1: Converting String to INT
SELECT PARSE(‘100.000’ AS INT) AS ValueInt
 select parse()

 –Example2: Converting String to Date/Time
SELECT PARSE(‘July 30, 2011’ AS DATETIME) AS ValueDT

2- Try_parse() Function

The TRY_PARSE() function can convert any string value to Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.

Example1: Converting String to INT

— No error
 SELECT PARSE(‘100.000’ AS INT) AS ValueInt;
 SELECT TRY_PARSE(‘100.000’ AS INT) AS ValueInt;
 — Error
 SELECT PARSE(‘sa300.000’ AS INT) AS ValueInt;
 SELECT TRY_PARSE(‘mostafa1985.10’ AS INT) AS ValueInt;

3-  TRY_CONVERT() function

This Function like Convert but convert function if you try to convert string to int it will result Error but TRY_COBVERT it will give you NULL like TRY_PARSE() Function .

—No Error
SELECT CONVERT(INT, ‘500’) AS ValueInt;
 SELECT TRY_CONVERT(INT, ‘500’) AS ValueInt;
—Error
 SELECT CONVERT(INT, ‘m100.000’) AS ValueInt;
 SELECT TRY_CONVERT(INT, ‘m100.000’) AS ValueInt;

Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()

Diffrence Between pars() and Try_Pars() Function : Parse if try to use it with one or more incorrect values will throw an error However, if you use TRY_PARSE function it will not throw error but will return parse string to  the result as NULL

difference between Convert() and TRY_CONVERT() functions: Convert function try to convert the string and return the value if it can if it can’t it will return Error , However Try_Convert Function it will try to convert the string and return value if it can but if it can’t it will return NULL.

Date and Time Functions In SQL Server 2012

Date and Time Functions In SQL Server 2012

DATEFROMPARTS ( year, month, day)
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
EOMONTH ()  — TO SHOW CND DATE IN MONTH
 set dateformat dmy
 select DATEFROMPARTS (1985,10,01)
 SELECT DATETIME2FROMPARTS (1985,10,1,11,31,00,0,0)
  SELECT DATETIMEFROMPARTS (1985,10,1,11,31,00,100)
 SELECT DATETIMEOFFSETFROMPARTS (1985,10,1,11,33,12,0,11,0,7)
 SELECT SMALLDATETIMEFROMPARTS (2010,12,31,23,59)
 SELECT TIMEFROMPARTS (23,59,59,0,0)
select EOMONTH (‘1985/10/1’)

Logical Function in SQL Server 2012

IIF() Function

IIF () Function is great function and all developer know this function because its already in VB.NET but the new it’s will be used it in SQL

IIF () Function Syntax

IIF(the Condition , ‘1’,’2′) if the condition is true the result will return no 1 , Else the result will return no 2

Examples

SELECT IIF ( -1 < 1, ‘TRUE’, ‘FALSE’ ) AS Result;
—Example 2
DECLARE @NAME NVARCHAR(15)= ‘MOSTAFA’
SELECT IIF(LEN(@NAME) > 5 , ‘BIG NAME’ , ‘SHORT NAME’) AS LONG
—Example 3
DECLARE @VARIBALE INT =   NULL
SELECT IIF (@VARIBALE IS NULL
, ‘YES’,’NO’)
—Example 4
 CREATE TABLE EMPLOYEE
 (
 EMP_NAME NVARCHAR(50),
 CODE NVARCHAR(50)
 )
 INSERT INTO EMPLOYEE VALUES (‘MOSTAFA’,’M100′),(‘ABDEL-KAREEM’,’100′),(‘OMAR’,’O100′)
SELECT EMP_NAME,CODE,IIF(TRY_PARSE(CODE AS INT) IS NULL , ‘VALUE IS STRING’ , ‘VALUE IS NOT STRING’)
  FROM EMPLOYEE

CHOOSE() FUNCTION

Choose Function is very simple function if the index is numeric it will convert to integer , and if the index is great than the element in the list it will return NULL

Example NO 1

 SELECT CHOOSE ( 1, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_First;

 SELECT CHOOSE ( 2, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Second;

 SELECT CHOOSE ( 3, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Third;

Example NO 2 (if the index is great than the elemant in the list )

SELECT CHOOSE ( 0, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Null;

 SELECT CHOOSE ( 4, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Result_NULL;

Example NO 3 ( if the indesx is numeric it will convert to integer )

 SELECT CHOOSE ( 1.1, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_First;

 SELECT CHOOSE ( 2.9, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Second;

String Function in SQL Server 2012

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;