TRY PARSE function in SQL Server 2012
sql server tryparse
try_parse in sql server 2012
try parse int sql server
In this video we will discuss
TRY_PARSE function
Difference between PARSE and TRY_PARSE functions
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
/ @aarvikitchen5572
TRY_PARSE function
Introduced in SQL Server 2012
Converts a string to Date/Time or Numeric type
Returns NULL if the provided string cannot be converted to the specified data type
Requires .NET Framework Common Language Runtime (CLR)
Syntax : TRY_PARSE ( string_value AS data_type )
Example : Convert string to INT. As the string can be converted to INT, the result will be 99 as expected.
SELECT TRY_PARSE('99' AS INT) AS Result
Example : Convert string to INT. The string cannot be converted to INT, so TRY_PARSE returns NULL
SELECT TRY_PARSE('ABC' AS INT) AS Result
Use CASE statement or IIF function to provide a meaningful error message instead of NULL when the conversion fails.
Example : Using CASE statement to provide a meaningful error message when the conversion fails.
SELECT
CASE WHEN TRY_PARSE('ABC' AS INT) IS NULL
THEN 'Conversion Failed'
ELSE 'Conversion Successful'
END AS Result
Output : As the conversion fails, you will now get a message 'Conversion Failed' instead of NULL
Example : Using IIF function to provide a meaningful error message when the conversion fails.
SELECT IIF(TRY_PARSE('ABC' AS INT) IS NULL, 'Conversion Failed',
'Conversion Successful') AS Result
What is the difference between PARSE and TRY_PARSE
PARSE will result in an error if the conversion fails, where as TRY_PARSE will return NULL instead of an error.
Since ABC cannot be converted to INT, PARSE will return an error
SELECT PARSE('ABC' AS INT) AS Result
Since ABC cannot be converted to INT, TRY_PARSE will return NULL instead of an error
SELECT TRY_PARSE('ABC' AS INT) AS Result
Example : Using TRY_PARSE() function with table data. We will use the following Employees table for this example.
SQL Script to create Employees table
Create table Employees
(
Id int primary key identity,
Name nvarchar(10),
Age nvarchar(10)
)
Go
Insert into Employees values ('Mark', '40')
Insert into Employees values ('John', '20')
Insert into Employees values ('Amy', 'THIRTY')
Insert into Employees values ('Ben', '21')
Insert into Employees values ('Sara', 'FIFTY')
Insert into Employees values ('David', '25')
Go
The data type of Age column is nvarchar. So string values like (THIRTY, FIFTY ) are also stored. Now, we want to write a query to convert the values in Age column to int and return along with the Employee name. Notice TRY_PARSE function returns NULL for the rows where age cannot be converted to INT.
SELECT Name, TRY_PARSE(Age AS INT) AS Age
FROM Employees
If you use PARSE instead of TRY_PARSE, the query fails with an error.
SELECT NAME, PARSE(Age AS INT) AS Age
FROM Employees
The above query returns the following error
Error converting string value 'THIRTY' into data type int using culture
Link for all dot net and sql server video tutorial playlists
https://www.youtube.com/user/kudvenka...
Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspo...
In questa pagina del sito puoi guardare il video online TRY PARSE function in SQL Server 2012 della durata di ore minuti seconda in buona qualità , che l'utente ha caricato kudvenkat 15 ottobre 2015, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 55,289 volte e gli è piaciuto 277 spettatori. Buona visione!
