TRY PARSE function in SQL Server 2012 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 123 of sequel server
tutorial in this video we'll discuss the
tryparse function and the difference
between pars and tryparse functions
tryparse function is introduced in
sequence over 2012 it's used to convert
a string to a the daytime or to any of
the numeric data types it returns now if
the provided string cannot be converted
to the specified destination data type
it requires a dotnet framework CLR that
is the common language runtime here is
the syntax for this function first we
specify the name of the function and
then the string value that we want to
convert to either date time or to any of
the numeric data types and then we use
the ask keyword followed by that our
destination data type let's look at an
example we want to use try parse
function though 99 is a number since
it's present within two single quotes
it's represented as a string here so
with try parse function we first specify
the string that we want to convert to a
the day time or to any of the numeric
data types and then we use the ask
keyword followed by that our destination
data type our destination data type is
going to be integer let's give this
column an earlier as let's call it
result so this 99 here the string value
can be converted to integer so in this
case tripe hours is going to return us
99 as expected but let's see what's
going to happen when we use ABC now this
string cannot be converted to integer no
it can't so in this case the conversion
is going to fail when the conversion
fails try parse is going to return now
it will not return an error it returns
now so when I execute this look at that
conversion fails and we get now we have
those two examples right here now you
can use a the case statement or if a
function to provide a meaningful error
message instead of null when the
conversion fails here we have an example
of using case statement to provide a
meaningful error message to the user
when the conversion fails let's look at
this in action so I'm going to use case
statement
so case when tryparse is now so whenever
it's trying to convert the string to
integer and if it returns null then we
want to display this message to the user
conversion failed else we want to
display conversion successful let's end
the case statement and let's give this
column an early as let's call it result
now can this ABC string be converted to
integer no it can't
so tryparse is going to return in this
case now so whatever is the result of
this tryparse function is it now if it
is now then it's going to return this
message conversion fail on the other
hand if the conversion is successful
then this expression will evaluate to
you know false so it's going to come to
the else part and it's going to return
this message conversion successful now
we expect conversion failed message to
be displayed there we go if we pass 99
for example instead of ABC conversion
should be successful so we expect
conversion successful to be displayed
there we go now here we use in case
statement instead of that we can also
use the if function so let's use that
instead of case so I'm going to use a
function with a function first we
specify the boolean expression so if
this boolean expression evaluates to
true then this is the true value that we
want to return if this boolean
expression evaluates to false this is
the false value that we want to return
and let's give this column an early as
let's call it result so the conversion
now should succeed so it should return
this message conversion successful and
if we pass ABC conversion fails and we
expect conversion failed message to be
displayed we have those two examples
right here now in previous versions of
sequel server we already have a function
called pars try pars is introduced in
sequence over 2012
so what's the difference between these
two functions well the difference is
parts will result in an error if the
conversion fails
whereas tryparse will return null
instead of an error let's actually prove
this with examples now first let's use
parse function so using this function I
want to convert ABC to integer now the
conversion is going to fail so in this
case we are using parse function so it's
going to return an error look at that we
get an error now if I use try parts
instead of parts it will not return an
error we get now and this is very useful
when working with tabular data let's
like to look at an example of using
typers function with table data so here
on the Left we have employees table
within this table we have got three
columns ID name and age if you look at
each column it's clearly is a string
column because notice along with you
know numbers we also have string values
like 30 and 50 now what we want to do is
convert the values in this age column to
integers and the reason for that is you
know we want to find out what is the
maximum age or minimum age or average
age if we have to do any of those you
know there is a need for these values to
be converted to integer let's try and do
that first by using tryparse function so
what we want to do is our query should
return name and the age of the employee
the age should be an integer format
within that column so let's see how to
achieve that first by using tryparse
function we have our employee table
right here so within our results that we
want name and then the age column so I'm
going to use try parse function convert
age to integer and let's give this
column an ALICE let's call it age okay
so look at what's going to happen when
we execute this query now wherever we
have string values that cannot be
converted to integer so what are the
string values that cannot be converted
to integer 30 and 50 so in those two
cases look at that we get now and in all
the other cases where the
string can be converted to integer we
get the integer values the age of
employees now you know on this result we
can easily use Max and min functions and
find out what is the maximum or some of
you know ages of all our employees now
let's see what's going to happen when we
use parse instead of try parts so let's
use parts and look at this when I
execute this query the query terminates
with an error and if you look at the
result here look at that it has returned
you know the first two rows successfully
but then when it came to the third row
it has seen this string that cannot be
converted to integer and the statement
has failed there and we get this message
right here error converting string value
30 into datatype end thank you for
listening and have a great day