Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

i-catching solutions
Browse by Tags · View All
SQL Server 26
Script toolbox 16
T-SQL 12
SQL 12
Geniiius 8
SQL Server 2012 7
Performance optimization 6
DMV 5
debugging 5
Debugging 5

Archive · View All
November 2011 6
May 2012 4
April 2012 4
January 2012 4
February 2012 3
March 2012 3
October 2011 2
December 2011 2
September 2012 1
June 2012 1

Geniiius's Blog

SQL Server 2012 – new conversion functions

Dec 20 2011 12:00AM by Geniiius   

With SQL Server 2012 comes a new set of conversion functions, that will certainly help doing type casts and conversions in t-sql. These functions are:

TRY_PARSE()

Syntax from BOL:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

This function works similar to the well known CAST, but will return NULL if the string_value does not represent a valid formatted value of the given data type.

Let me show a few examples:

select try_parse('2011-12-24' as date)

 

This results in the following output, meaning that the value ‘2011-12-24’ is a valid formatted date:

image

If we try this:

select try_parse('2011-02-30' as date)

 

The result is NULL as expected, since 30th of february is not a valid date:

image

Another cool thing is that we can give it a culture, so we can convert date written in different locals. Let me demonstrate:

 

SELECT TRY_PARSE('11-12-2011' AS datetime USING 'en-US') AS Result
SELECT TRY_PARSE('11-12-2011' AS datetime USING 'da-DK') AS Result

The input value is identical ‘11-12-2011’, but the output is different given the two different locals:

image

Now it could be tempting to replace all use of CAST to TRY_PARSE, but BOL states that TRY_PARSE should only be used for converting from strings to date/time and number types. Also the function relies on the presence of the .NET CLR, and that the parsing has a small overhead. Never the less it gives you more control of the input formats, as well as the capability of handling wrong formatted input without throwing errors.

PARSE()

Syntax from BOL:

PARSE ( string_value AS data_type [ USING culture ] )

This works pretty much the same as TRY_PARSE, but this function will throw an error if the string_value passed to the function is not a valid representation of the data type. Let me show you the similar parsings as above:

SELECT PARSE('13-12-2011' AS datetime USING 'da-DK') AS Result
SELECT PARSE('13-12-2011' AS datetime USING 'en-US') AS Result

The result is a bit different:

image

The first results in a date, because the input format is ok for da-DK, but the second one is not, because it means the 13th month. Instead of returning NULL as the TRY_PARSE would do, it throws an error:

(1 row(s) affected)
Msg 9819, Level 16, State 1, Line 2
Error converting string value '13-12-2011' into data type datetime using culture 'en-US'.

TRY_CONVERT()

Syntax from BOL:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

This is pretty much the same as the well known CONVERT, but again with the NULL output for invalid input strings.

Lets see how the old CONVERT would handle these two:

SELECT CONVERT(XML, '<root>Hello World!</root>') AS MyXML
SELECT CONVERT(XML, '<root>Hello World!) AS MyXML

(Notice the missing </root> element in the second select). The output is this:

image

The first one converts to XML just fine, but the second doesn’t:

(1 row(s) affected)
Msg 9400, Level 16, State 1, Line 2
XML parsing: line 1, character 18, unexpected end of input

Let’s try the same with the new TRY_CONVERT instead:

SELECT TRY_CONVERT(XML, '<root>Hello World!</root>') AS MyXML
SELECT TRY_CONVERT(XML, '<root>Hello World!') AS MyXML

The output:

image

And no errors in the message tab.

These are just minor aditions, but I’m sure they will find there use around. Have you ever tried identifying a non-integer value in a varchar column?

Let’s create a dummy table for demonstration:

CREATE TABLE MyBadTableDesign
(
    id INT IDENTITY PRIMARY KEY,
    val varchar(10) --This will hold integer values
)
GO

INSERT INTO MyBadTableDesign (val)
VALUES
    ('1'),
    ('2'),
    ('3'),
    ('4'),
    ('s'),
    ('6'),
    ('t'),
    ('8')
GO

Now we have a table with a val column of type varchar(10). It was supposed to contain only integers (hence my BadTableDesign name…), but unfortunately some non-integer data has entered the table. This gives us a problem, if/when we try to cast the column to an INT:

SELECT id, CAST(val AS INT) AS Val FROM MyBadTableDesign

This gives me this partial result:

image

And the following error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 's' to data type int.

But what if there were million of rows in the table, and multiple non-integer values? It would not be pleasant to try to identify them all. But with TRY_PARSE we can now do this to identify the rows:

SELECT * FROM MyBadTableDesign
WHERE TRY_PARSE(val AS INT) IS NULL

And this gives us exactly the rows that could not be parsed to an INT

image

I know I have been in a situation like that before!

Republished with author's permission. See the original post here.


Republished from geniiius.com [45 clicks].  Read the original version here [32134 clicks].

Geniiius
134 · 1% · 369
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"SQL Server 2012 – new conversion functions" rated 5 out of 5 by 3 readers
SQL Server 2012 – new conversion functions , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]