How to PARSE In SQL Server

Since SQL Server 2012 there are two new T-SQL commands for parsing data to different data types. They are called PARSE and TRY_PARSE and are really useful, but there are some thing you need to be aware of.

PARSE and TRY_PARSE will parse a string to another datatype depending on the language culture specified. For example you could convert a datetime string to datetime type:

SELECT PARSE(‘Monday, 13 December 2010‘ AS datetime2 using ‘en-US’) AS Result

SELECT PARSE(‘Måndag 13 December 2010’ AS datetime2 using ‘sv-SE’) AS Result

SELECT PARSE(‘Måndag den 13 December 2010’ AS datetime2 using ‘sv-SE’) AS Result

SELECT PARSE(‘måndag 13 december 2010 kl 10:12’ AS datetime2 using ‘sv-SE’) AS Result

SELECT PARSE(‘måndag 13 december 2010 klockan 10:12’ AS datetime2 using ‘sv-SE’) AS Result — Error!

You can also convert numerical or currency strings to numerical values:

SELECT PARSE(‘€345,98′ AS money using ‘de-DE’) AS Result

SELECT PARSE(‘£345,98′ AS money using ‘de-DE’) AS Result     — Error!

SELECT TRY_PARSE(‘£345,98’ AS money using ‘de-DE’) AS Result

SELECT TRY_PARSE(‘€345,98’ AS money using ‘de-DE’) AS Result

As you can see, the currency symbol must match the language culture, otherwise you’ll get an error.

If you do not specify the language culture, SQL Server will use the default language culture of your SQL Server instance. To check the default, you can use the following command to see which culture you are using:

SELECT @@LANGUAGE

The next thing you need to be aware of is how the thousand separator is handled:

SELECT PARSE(‘1.53’ AS DECIMAL(10,2)) AS Result

SELECT PARSE(‘1,53’ AS DECIMAL(10,2)) AS Result

SELECT TRY_PARSE(‘1,53’ AS DECIMAL(10,2)) AS Result

SELECT PARSE(‘1,53’ AS DECIMAL(10,2) USING ‘sv-SE’) AS Result

In this sample you can see that the comma in the second and third statement is ignored as it is a thousand separator in US/EN language culture. You might have expected the value 1,53 but you get 153.

A common problem in using String datatypes for date or numerical values Is that malformatted data may casue issues when trying to convert them (implicitly or explicitly). By using TRY_PARSE, you can track the malformatted data.

—- Using TRY_PARSE identify malformed data

—————————————————–

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=’t’ AND TABLE_SCHEMA=’dbo’)

DROP TABLE dbo.t;

CREATE TABLE t (DateText VARCHAR(20));

INSERT INTO t(DateText) VALUES (‘2007-02-02’),(‘2007-04-03’),(‘2007-03-12’),(‘2007-0-511’)

DECLARE @d DATE = ‘2007-02-02’;

SELECT COUNT(*) FROM t WHERE DateText > @d; –DateText cannot be converted due to the malformatted date

SELECT COUNT(*) FROM t WHERE DateText > CONVERT(varchar(20),@d);

SELECT * FROM t WHERE TRY_PARSE(DateText AS DATE USING ‘sv-SE’) IS NULL;

SELECT * FROM t WHERE TRY_CONVERT(DATE, DateText,120) IS NULL;

There are lots of other additions to the T-SQL language in SQL Server 2012 and if you want to know more, feel free to contact us or post a comment below.