jump to navigation

Looking into the new conversion functions in SQL Server 2012 July 15, 2012

Posted by fofo in Sql Server, SQL Server 2012.
Tags: , ,
trackback

I will be writing a new series of posts about the new T-SQL functions that are available to us in SQL Server 2012.

In this rather sort post I will demonstrate with a hands-on examples how to use 3 new conversion functions in SQL Server 2012, PARSE, TRY_CONVERT and TRY_PARSE.

You can have a look in another blog post of mine related to built-in SQL functions.You can have a look into the new logical functions introduced in SQL Server 2012, here .

You can use any edition of SQL Server of 2012 you want. SQL Server Express edition will do just fine.Download the Express edition, here .

In my case I will use Developer edition of 2012 for these examples.

I will look into the PARSE() function first.We can convert a string value to numeric or datetime format with the PARSE function.If that is not possible an error will be returned.We must say that this function relies on CLR to do the conversions.If there is no CLR an error will be returned.

This function takes a string value as the first argument and as a second argument there is the data type that we want to convert the value in the first argument.The third argument (optional) is the culture in which the string value will be formatted.

Type and execute the code below


SELECT PARSE ('THURSDAY, 30 JANUARY 2014' AS datetime2 USING 'en-us') AS NEWDAY
SELECT PARSE('€345,98' AS money USING 'fr-FR') AS Newmoney
SELECT PARSE('nick' AS datetime2 USING 'en-GB') AS Res

The first two statements will succeed but the third one will fail.

Let’s try this new function in a table

Type and execute the code below


USE tempdb;
GO

CREATE TABLE [dbo].[Employee](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](30) NOT NULL,
[LastName] [nvarchar](30) NOT NULL,
[Country] [nvarchar] (30) NOT NULL,
[City] [nvarchar] (30) NOT NULL,
[PostalCode] [nvarchar] (30) NOT NULL,
[HireDate] [date] NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

INSERT INTO Employee (FirstName, LastName,Country,City,PostalCode,HireDate)
VALUES
('Robbie', 'Fowler','USA', 'New York', '434343','1/10/2004'),
('Steven', 'Gerrard','Ireland','Dublin','75676','10/3/2000'),
('Michael', 'Owen', 'Britain','London','87677','2/4/2002')

SELECT FirstName,LastName,PARSE(PostalCode as int) newpostalcode FROM Employee

I create a table and I insert some values in it. Then in the last statement I parse the postal code values from string to int.

The next function I will be looking into is TRY_PARSE.It is very similar with the PARSE function. We can convert a string value to numeric or datetime format with the TRY_PARSE function.If that is not possible an NULL value will be returned.We must say that this function relies on CLR to do the conversions.If there is no CLR an error will be returned.

This function takes a string value as the first argument and as a second argument there is the data type that we want to convert the value in the first argument.The third argument (optional) is the culture in which the string value will be formatted.

Type and execute the code below


SELECT TRY_PARSE ('THURSDAY, 30 JANUARY 2014' AS datetime2 USING 'en-us') AS NEWDAY
SELECT TRY_PARSE('€345,98' AS money USING 'fr-FR') AS Newmoney
SELECT TRY_PARSE('nick' AS datetime2 USING 'en-GB') AS Res

The first two statements will succeed but the third one will fail and we will receive a NULL value as a result.

Type and execute the code below


USE tempdb;
GO
SELECT FirstName,LastName,TRY_PARSE(PostalCode as int) newpostalcode FROM Employee
SELECT FirstName,LastName,TRY_PARSE(Country as int) newcountry FROM Employee

The first  statement will succeed but the second one will fail and we will receive NULL values as a result.

The final function I will be explaining is the TRY_CONVERT function.

TRY_CONVERT takes a value that is passed to it and tries to convert it to the specified data type.

If the cast succeeds, TRY_CONVERT returns the value as the specified data type.On the other hand if an error occurs, NULL is returned.if you request a conversion that is explicitly not permitted, then TRY_CONVERT will fail with an error.The first argument is the data type into which to cast the expression (value to be cast).

Type and execute the code below


SELECT TRY_CONVERT(datetime2, '12/12/2010') AS Result;
SELECT TRY_CONVERT(datetime2, '32/12/2010') AS Result;
SELECT TRY_CONVERT(int, 'nick' ) AS Res

The first statement will succeed but the second and third statement will fail and we will get as a result of that a NULL value.

Type and execute the code below


USE tempdb;
GO
SELECT FirstName,LastName,TRY_CONVERT(int,PostalCode) newpostalcode FROM Employee
SELECT FirstName,LastName,TRY_CONVERT(INT, Country) newpostalcode FROM Employee

The first  statement will succeed but the second one will fail and we will receive NULL values as a result.

Type and execute the code below


SELECT TRY_CONVERT(xml, 6677) AS Res;
GO

This statement will return an error.To recap we saw 3 new conversion functions introduced in SQL Server 2012.

Hope it helps!!!

About these ads

Comments»

1. Dot Net Rules : Looking into the new conversion functions in SQL Server 2012 - July 15, 2012

[...] how to use 3 new conversion functions in SQL Server 2012, PARSE, TRY_CONVERT and TRY_PARSE.(read more) Share Posted: Κυριακή, 15 Ιουλίου 2012 4:01 πμ από το μέλος [...]

2. Looking into Concat string function in SQL Server 2012 « DOT NET RULES - July 16, 2012

[...] can have a look in another blog post of mine explaining the new conversion functions in SQL Server [...]


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

Follow

Get every new post delivered to your Inbox.

Join 1,794 other followers

%d bloggers like this: