3 min læsning

New string conversion functions

Featured Image

SQL Server 2012 contains two new string conversion functions. PARSE and TRY_PARSE. These two functions make life a lot easier when you want to convert a string to another data type.

The different between the two functions are that PARSE will return an error if the conversion fails. TRY_PARSE will return NULL.

Some examples:

<span style="color:green;">/* Returns the same results */
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">PARSE</span><span style="color:gray;">(</span><span style="color:red;">'28 dec 1977' </span><span style="color:blue;">AS date</span><span style="color:gray;">)
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'28 dec 1977' </span><span style="color:blue;">AS date</span><span style="color:gray;">)

</span><span style="color:green;">/* PARSE will return the error
   Error converting string value '30 feb 1977' into data type date using culture  */
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">PARSE</span><span style="color:gray;">(</span><span style="color:red;">'30 feb 1977' </span><span style="color:blue;">AS date</span><span style="color:gray;">)
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'30 feb 1977' </span><span style="color:blue;">AS date</span><span style="color:gray;">)
</span>

It is possible to tell PARSE and TRY_PARSE if your input string-date is formatted in another regional setting ex. month day year.

<span style="color:green;">/* Returns the same result 3th may 1977 */
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'03-05-1977' </span><span style="color:blue;">AS date USING </span><span style="color:red;">'da-DK'</span><span style="color:gray;">) </span><span style="color:blue;">AS </span><span style="color:teal;">Result
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'05-03-1977' </span><span style="color:blue;">AS date USING </span><span style="color:red;">'en-US'</span><span style="color:gray;">) </span><span style="color:blue;">AS </span><span style="color:teal;">Result
</span>

Finally an example on how to convert a string to an tinyint.

<span style="color:green;">/* The last TRY_PARSE return NULL while 2222 is larger than TINYINT */</span>
<span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'22'</span><span style="color:blue;">AS int</span><span style="color:gray;">)
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'2222'</span><span style="color:blue;">AS tinyint</span><span style="color:gray;">)</span>

In some cases the right thing to do is to raise an error. In that case use PARSE. In most cases I think I’ll go with TRY_PARSE.