TRY CONVERT function in SQL Server 2012 Video Lecture - SQL Server Administration

this is part 124 of signal server
tutorial in this video we'll discuss try
convert function difference between
convert and try convert functions and
try parts and try convert functions try
convert function is introduced in secret
so 2012 it converts a value to the
specified datatype it returns null if
the provided value cannot be converted
to the specified destination datatype if
you request a conversion that is
explicitly not permitted then try
convert fails with an error we have the
syntax for this function right here the
first parameter is the destination
datatype that is the data type to which
we want to convert a value the second
parameter is the value itself that we
want to convert and the third parameter
is style which is optional the range of
acceptable values for this parameter is
determined by the destination datatype
for the list of all possible values for
this parameter please visit this MSDN
article right here
I'll have this link available on my blog
the values and the usage of this style
parameter is same as that of the convert
function so I'm not going to go into the
details of using this style parameter
let's look at an example of using the
strike and word function so with try
convert function the first parameter is
the destination data type so we want to
convert a value to integer and the
second parameter is the value of itself
so in this case we want to convert the
string 99 to India the conversion is
going to succeed so when we execute this
query we should get 99 I have this exact
same query here so when we execute that
notice we get 99 as expected now let's
look at another example in this case we
are trying to convert this string ABC to
integer this conversion is going to fail
when the conversion fails try convert
will not return an error instead it
returns now so when we execute this
query we should get no now there are
scenarios when a conversion is not
explicitly permitted for example
converting an integer to XML is not
explicitly permitted in this case try
convert is going to fail with an error
so if you look at the query here we are
trying to convert this integer 10 to XML
which is not explicitly permitted so
this query is going to fail with an era
so when we execute this notice we don't
get now instead it straightaway fails
with an error saying explicit conversion
from data type end to XML is not allowed
case statement of F function can be used
to provide a meaningful error message
instead of null and the conversion fails
so here we are using the case statement
we are trying to convert this string ABC
to integer so this conversion is going
to fail when the conversion fails try
convert returns now so when try convert
is now that means the conversion has
failed at that point we want to display
conversion failed message if try convert
has not returned null then we know that
the conversion has succeeded in that
case we want to display this message
conversion successful this is the usage
of case statement and here we are using
the if function to do exactly the same
thing I have these two queries already
typed here so notice here we are trying
to convert ABC to integer the conversion
is going to fail and when we execute
that notice we get the message
conversion fail now if we use 99 instead
of ABC the conversion is going to
succeed so in this case it should say
conversion successful and here the same
idea but we are using the F function so
again we're trying to convert ABC to
integer when we execute this conversion
fail if we use 99
it should say conversion successful so
what's the difference between convert
and try convert functions the difference
between convert and try convert
functions is the same as the difference
between pars and tryparse functions
which we discussed in our previous video
session convert will result in an error
if the conversion fails whereas try
convert will return null instead of an
error when the conversion fails and we
have examples here so here we are using
the convert function and we are trying
to convert the string ABC to integer so
the conversion is going to fail since we
are using the convert function it's
going to return an error whereas if we
use try convert to do the same thing
this function will not return an error
instead it will return now I have these
two queries right here so firstly using
the convert function look at this when I
execute it we get an error straight away
but when I use try convert instead of
convert it's not going to raise an error
but we get now now let's look at an
example of using this try convert
function with table data so on the Left
we have employees table notice the age
column this is an N where care column so
that's the reason why it's allowing us
to store these strings 30 and 50 as the
values within this column now what we
want to do is convert these values in
this column to integer first let's try
to do that using Tripa our convert so
here we're using the try convert
function and we want to convert the
values of the age column to integer so
when we execute this this the result
that we get notice wherever we have
those strings 30 and 50 you know those
strings cannot be converted to integer
so we get nulls there but all the other
strings where we have you know a string
value that can be converted to integer
we get the corresponding integer values
I have that query right here
so when I execute this query
where we are using try convert notice
that we get the values that we have seen
on the slide but when I use convert to
do the same thing look at what is going
to happen the query actually fails with
an error
look at that and we got two rows mark
and John those are the first two rows
the third row contain this value 30 okay
that cannot be converted to integer and
since we are using the convert function
it fails with an error and it stops
right there but whereas the try convert
we don't get an error it returns now and
then it moves on to the next row so now
let's look at the difference between try
bars and try convert functions tryparse
which we discussed in our previous video
session it can only be used for
converting from string to daytime or any
numeric data types
whereas try convert can be used for any
general type conversions for example you
can use try convert to convert a
stranger to the XML data type whereas
you cannot do the same using tryparse
so here we're using trike
but and we're trying to convert the
string right here to an XML data type
okay this is going to succeed but when
you use try parts to do the same thing
to convert this string as an XML you
know it's not going to I love that
notice what is their message says
invalid data type XML in function try
parts try parts can only be used to
parse a string to either date time or to
any of the numeric data types for any
other conversions you will have to use
try convert okay so I have those two
queries right here look at that when I
use try can work to convert that string
to XML it works look at that I have the
XML there if I try to do it using try
parts look at that we get an error
message it fails another difference
between try parts and try convert is
that try parts relies on the presence of
dotnet framework siela that is the
common language runtime
whereas try convert does not thank you
for listening and have a great day