User Id :    Password :      New Member   Forgot Password  
 
Check whether an expression is numeric type in sql server
Description How to check whether an expression is numeric type in sql server.   No. of Views     2136
  Rating     0
Author Ferhan Siddiqui   Posted On     21 Oct 2010
Tags SQL SERVER,SQL SERVER : How To    

Sample Code   Download Code

Introduction

Sometimes we need to convert an expression in numeric values, but it caused exception if the expression is not a valid numeric type.

Like in the example below

DECLARE @Expression VARCHAR(20)
SET @Expression='123A'
SELECT CONVERT(INT,@Expression)

The last statement cause an exception because it is trying to convert an VARCHAR type to INT.

To avoid this exception its better to check the expression is a valid numeric type.

DECLARE @Expression VARCHAR(20)
SET @Expression='123A'

IF ISNUMERIC(@Expression)=1
    SELECT CONVERT(INT,@Expression)
ELSE
   SELECT 'Invalid type conversion'

SET @Expression='123'
IF ISNUMERIC(@Expression)=1
    SELECT CONVERT(INT,@Expression)
ELSE
    SELECT 'Invalid type conversion'

 

OUTPUT                               
Invalid type conversion                       
123                                                         


Thanks

 

About Author

About Author I m a software developer having more than 3.5+ years experience. Ferhan
No Photo
 
Country N/A
Company Saffron Infotech Private Limited
Home Page N/A

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

   
Write your comment here.
Comment
Verification Code