Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Validate Integer and Decimal Values in SQL Server


By:   |   Read Comments (2)   |   Related Tips: More > Functions - User Defined UDF

Problem

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?

Solution

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.

CREATE FUNCTION dbo.IsInt
(
@number VARCHAR(20)
)
RETURNS BIT
AS
BEGIN

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

END

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.

CREATE FUNCTION dbo.IsABSdecimal
(
@number VARCHAR(100)
)
RETURNS BIT
AS
BEGIN

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

END

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.

CREATE FUNCTION dbo.Isdecimal
(
@number VARCHAR(100)
)
RETURNS BIT
AS
BEGIN

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

END

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


Last Update:






About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel has been working with SQL Server since 2007 as a Senior DBA.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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

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)...


Learn more about SQL Server tools