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.
Below is an example of the SQL Server ISNUMERIC system function use cases:
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.
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.
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.
Based on this logic, I got the results as expected. It has validated the decimal values.
Next Steps
- Reference this link for more clarification: check valid decimal and integer values
- Check out the Isnumeric using SSIS: Validate Isnumeric function using SSIS
- Please test in a lower life cycle environment prior to rolling out this code in production.
Bhavesh Patel is a SQL Server database professional with 10+ years of experience. Most of his career has been working on database development and administration. He has a strong interest in continuous learning and likes facing challenges to learn new things. In his spare time, he spends time with his family. Currently, his main role is query tuning with respect to optimization and server performance.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2019 | Rookie of the Year Contender – 2017
