SQL Server Portal

SQL SERVER 2012 has a new built in function namely “PARSE”. Lets discuss its syntax , purpose and examples in detail.

PARSE

Syntax :

PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function converts the string value (nvarchar(4000) data type) to datetime or numeric data types.
We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

Example-1 : PARSE -Varchar to Numeric

SELECT PARSE('123.45' AS NUMERIC(18,2)) AS Output
--Result
123.45

Example-2 : PARSE – Varchar to Int
Lets use the same example of numeric and cast it to integer.

SELECT PARSE('123.45' AS INT) AS Output
--Result

Msg 9819, Level 16, State 1, Line 1
Error converting string value ‘123.45’ into data type int using culture .
Note : The above line of code will generate error because whatever we want to parse should be compatible with the parse data type.

Now, lets do the same without scale.

SELECT PARSE('123' AS INT) AS Output
--Result
123

Example-3 : PARSE – Varchar to Datetime with Default culture.

Select Parse('Aug 26 2012' as datetime)
Select Parse('08/26/2012' as datetime )
Select Parse('2012.08.26' as datetime)
Select Parse('26 Aug 2012' as datetime)
Select Parse('Aug 26, 2012' as datetime)
Select Parse('08-26-2012' as datetime)
Select Parse('2012/08/26' as datetime)
--Result of the above statements
2012-08-26 00:00:00.000

Example-4 : PARSE – Varchar to Datetime with culture

Select Parse('26/08/2012' as datetime using 'fr-FR')  --French
Select Parse('26.08.2012' as datetime using 'de-DE')  --German
Select Parse('26-08-2012' as datetime using 'it-IT')  --Italian
--Result of the above statements
2012-08-26 00:00:00.000

Example-5 : PARSE – Varchar to Currency with culture

Select Parse('$123.45' as money using 'en-US')
Select Parse('€123,45' AS money using 'de-DE')
--Result of the above statements
123.45

Example-6 : PARSE – Language Setting Instead of Culture

SET LANGUAGE 'French'
Select Parse('26/08/2012' as datetime)

SET LANGUAGE 'German'
Select Parse('26.08.2012' AS datetime2) AS Result

SET LANGUAGE 'Italian'
Select Parse('26-08-2012' as datetime)
--Result of the above statements
2012-08-26 00:00:00.000

Error :
If the string value is invalid to convert it into date time, numeric & any specific culture format, then it will generate an error.

Reference : MSDN