Check if Value is Numeric by using ISNUMERIC & TRY_Convert Function in SQL Server - TSQL Tutorial

How to Determine if value is Numeric by using ISNumeric and Try_Convert Function in SQL Server - TSQL Tutorial
ISNUMERIC( ) function is provided to us in SQL Server to check if the expression is valid numeric type or not. As per Microsoft it should work with Integers and Decimal data types and if data is valid integer or decimal, ISNumeric() should return us 1 else 0. But ISNUMERIC() does not work as expected with some of values specially when we have "-" or "d" in value and have two numbers after "d" such as 123d22, it still return us 1. Also if we have data in money format $XXXXX e.g $2000, It returns us 1.

In SQL Server 2012. Microsoft introduced new function call Try_Convert( ). You can use try_convert function to convert to required data type and if it is not able to convert then it will return Null as output. As you will see below, I did some experiment and found out that Try_Convert will produce 0 for "-" when we try to convert to Int, That should not be happening as "-" is symbol not Integer. But when I try to convert "-" to decimal, Try_Convert produced Null output.

Take a look in below results and keep in mind the outputs when you have to evaluate expression to Numeric or find out if expression is Numeric or Not.

Blog post link for this video

[sanc]

You did not posted user defined function ..pls post