Validate Integer and Decimal Values in SQL Server

By:   |   Comments (6)   |   Related: > Functions User Defined UDF


I have a CustomerDetails table with 15 columns in my SQL Server database. I need to insert data from a CSV file into the CustomerDetails table and have created a stored procedure called Import_CustomerDetails. In the stored procedure there is validation logic for positive decimal, negative decimal and positive integer values. I used the SQL Server ISNUMERIC system function for validation, but I couldn't validate the data as required because the ISNUMERIC function returns 1 for some characters that are not numbers such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).  So, how can I validate data with these use cases?


Let me further explain my situation with additional examples.  Here is a sample file and input columns for demo purposes.  With this sample data set the custcontact column should be positive integer values. The latitude column should be positive decimal values and the longitude column should be a negative decimal value, for my purposes. I didn't need to validate the dollar sign character (i.e. $), but this file was created by multiple users manually so the wrong value may have been entered in the file.

Sample Data

Below is an example of the SQL Server ISNUMERIC system function use cases:

SQL Server IsNumeric Evalation

As shown above, the ISNUMERIC function returns 1 for some values that are not strictly numbers. The function returns 1 for numbers that include symbols like +, -, $, etc. As per my use case, I need to validate decimal and integer values.

SQL Server User Defined Functions for Integer and Decimal Validation

Next, I will create new user defined functions to validate integer and decimal values as per my use case.

First I will create the dbo.IsInt scalar function to validate a positive integer value.

@number VARCHAR(20)

   RETURN ISNUMERIC(Replace(Replace(@number,'+','A'),'-','A') + '.0e0')


In this code, I replace symbols (i.e. +, -) with the 'A' character in lieu of specific characters in which the ISNUMERIC function has returned 1 (true) in addition I have concated the '.0e0' exponential value which is a floating point notation trick. Now I can validate a positive integer value without any symbols.

Now I am going to call this function for demo purposes to validate the data in the custcontact column.

SQL Server dbo.IsInt User Defined Function Results

Now, I have a single positive integer value to meet my requirements.

Next, I am creating the dbo.IsABSdecimal function to validate positive decimal values.

@number VARCHAR(100)

   RETURN REPLACE(ISNUMERIC(REPLACE(REPLACE(@number,'+','A'),'-','A') + 'e0'),1,CHARINDEX('.',@number))


This logic builds off the previous function's REPLACE logic and adds the charindex logic to find '.' with a bit a as return type which is Boolean.

Now I am going to call this function for demo purposes to validate the data in the latitude column.

SQL Server dbo.IsABSDecimal User Defined Function Results

Based on this logic, I got result as expected. It has validated the positive decimal value.

Now I am creating the dboIsDecimal function to validate all decimal values.

@number VARCHAR(100)

   RETURN REPLACE(ISNUMERIC(REPLACE(@number,'+','A') + 'e0'),1,CHARINDEX('.',@number))


Now I am going to call this function for demo purposes to validate the data in the longitude column.

SQL Server dbo.IsDecimal User Defined Function Results

Based on this logic, I got the results as expected. It has validated the decimal values.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, June 1, 2017 - 2:25:48 PM - Greg Robidoux Back To Top (56429)

Thanks Gerald and Scott.

To show others an example of Gerald's, I combined what Gerald and Scott contributed.


declare @TestString VARCHAR(128) = '123456789O' --Capital 'O' at the end

print @Teststring

IF @TestString NOT LIKE '%[^0123456789]%' print 'This is an integer!'

ELSE print 'Not an integer!!!'

Thursday, June 1, 2017 - 1:58:30 PM - Gerald Britton Back To Top (56428)

 I use the simple:


NOT LIKE '%[^0123456789]%'


to test for numerics.  Basically if there is any character that is not in 0-9, it is not an integer, otherwise it is


Wednesday, May 31, 2017 - 3:29:50 PM - Scott C. Smith Back To Top (56368)


 --Try to avoid using user defined functions whenever possible. They can bog down with big queries.

 --The example code below uses intrinsic SQL functions to test an integer and is fast:


 declare @TestString VARCHAR(128) = '123456789O' --Capital 'O' at the end

 print @Teststring

 IF @TestString LIKE REPLICATE('[1234567890]', LEN(@TestString)) print 'This is bona fide integer!'

 ELSE print 'Not!!!'


 set @TestString = '1234567890' --a valid integer

 print @Teststring

 IF @TestString LIKE REPLICATE('[1234567890]', LEN(@TestString)) print 'This is bona fide integer!'

 ELSE print 'Not!!!'


Wednesday, May 31, 2017 - 12:44:09 PM - Scott Smith Back To Top (56357)

 --a slick test for integer:

  IF @TestString NOT LIKE REPLICATE('[1234567890]', LEN(@TestString)) RETURN 0 --test for integer values



Friday, May 12, 2017 - 2:49:44 AM - Bhavesh patel Back To Top (55689)

Thanks @Thomas,

This newly function try_convert() is good when need to check datatype wise validation but here my all usecases is not covered by try_convert.
Here I need to check value specific validation and also want to ignore such symbol(+) with value.For example, My usecase is differed when I directly use
try_convert(int,'+454334'),try_convert(decimal,25) because both are validate value.

Here my purpose is that ,Developer should be aware when used isnumeric function for validate value.





Thursday, May 11, 2017 - 4:47:06 AM - Thomas Franz Back To Top (55670)

I usually prefer to use TRY_CAST() or TRY_CONVERT(), which would return NULL, when the parameter could not converted into the declared data type.

It is much faster than a scalar function (could be bypassed when you convert your function into an inline table value function). Drawback: it is less flexible, e.g. it recognizes '+94.412306' as valid (on the other hand, I don't know, why it should not)...

get free sql tips
agree to terms