validation using sql server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

validation using sql server

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-06 : 01:21:21

Hi,
I have an excel sheet with 5 columns.
I am exporting this excel sheet into a table of sql server having 5 same column names.
After that,I will have 400 rows in my sql server table. Now, I want to validate this criteria for all the 400 rows of data:
1) All dates in the data provided by the chapters will follow mm/dd/yyyy format (Example: 03/08/2012).
2) Gift amount field will not contain “$” and “,” and will be made up of only numbers with a decimal (e.g., 9999.00).
3) The account ids or constituent ids will be expressed in whole numbers with no spaces and not in scientific notation
4) All flag/ indicator fields in the specifications will follow Y/N format.
I want to write an sql stored proc for this. Input will be the sql table with all the fields. Output will be names of all the fields which dont meet the above mentioned criteria and also the reason indicating why the criteria is not met.

Can anyone help me?
Thanks in anticipation,
Mavericky

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-06 : 06:36:59

You can test for each of these conditions you have listed using like clauses against the table. For example:


-- 1. All dates are in MM/dd/yyyy format
SELECT
   dateCol,
   CASE WHEN dateCol LIKE '[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]' THEN 'Valid' ELSE 'Invalid' END
FROM
   YourTable;

-- 2 Numbers should have only digits 0-9 and period.
SELECT
   numberCol,
   CASE WHEN numberCol NOT LIKE '%[^0-9.]%' THEN 'Valid' ELSE 'Invalid'  END
FROM
   YourTable;

But writing a stored procedure to pass in an input table and get names of fields and the criteria poses some challenges. For example:

a) If you want to pass in the name of a table, you probably will need dynamic SQL. Not recommended if you can avoid it due to security considerations.

b) Your data may have all kinds of inconsistencies - for example, the date field may be in YYYYMMDD format, or YYYY-MM-DD format and so on. What kind of output would you expect that describes all of those?

As an aside, unless this is a staging table, you should store the date information using one of the DATE/DATETIME data types.

Many of these tests can be more precisely and accurately done using regular expressions. For example, the test that I have above for dates would allow dates such as 24/24/2012, but with a true regular expression you can control all of that. Unfortunately, T-SQL does not have a native implementation of regular expressions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-06 : 14:17:34

see similar CLR function here

http://www.codeproject.com/Articles/85954/Use-RegEx-in-SQL-with-CLR-Procs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-06 : 15:41:24

Hi sunitabeck,
Thanks for your reply. Regarding date format, that is fine for now if i can't catch a particular format like YYYYMMDD. My main concern is getting an output which shows names of all the fields which dont meet the above mentioned criteria and also the reason indicating why the criteria is not met. If we don't consider security, is it possible to achieve what i want?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-06 : 17:17:59

One possible approach is to create a temporary table with the same columns as the staging table, but with another additional column to indicate which column is invalid. Then, you could run the query like I had posted below to populate that table.

INSERT INTO #ErrorTable
SELECT
   *,
   'Invalid Date Column' as ErrorColumn
FROM
   YourTable
WHERE
   dateCol NOT LIKE '[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]';

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-06 : 17:46:47

This is helpful!! Thanks sunitabeck!!

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-06 : 17:47:24

thanks for the reply visakh16 but i am not using .net here..i have to do it in sql server only

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-06 : 17:50:48

dont have direct way to do it in t-sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-07 : 11:10:34

Also, I want to validate these conditions, can you suggest the sql code for the same?
-validation for first name: if somebody's first name is 't c' or 'tom c' or 'tom' it should be considered correct, but if the first name is tom cruise, it should be rejected
-middle name: 'R' is fine. 'Robert' is also fine, but 'Robert Carl' is incorrect
-last name: same rule as middle name
-DOB_Day: only values from 0 to 31 to be allowed
-DOB_Month: only values from 0 to 12 to be allowed
-DOB_Year: only 4 digit values should be allowed
-Flag: only 'Y' or 'N' is allowed.
-Status: only 'A' or 'I' is allowed.
-State: Only 50 US states should be allowed with 2 digit size abbreviations.
-Zip Code: 60612-0344. This should be the standard length

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-07 : 11:20:10

also validation for email address is required

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-07 : 11:48:21

quote:
Originally posted by mavericky

also validation for email address is required

Email validation in SQL is very, very difficult to do properly. You might be able to get by with some very basic checks, but doing to properly will probalby make your head explode. :)

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-07 : 12:06:07

Thanks Lamprey, but any idea of the above fields i asked earlier?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-07 : 14:11:25

If you need to do all those validations reliably, may be you do need regular expressions via CLR as Visakh had suggested. Otherwise, you can do some checks - may not be perfect. For example, for first name:

CASE WHEN LTRIM(RTRIM(@x)) NOT LIKE '% %' OR LTRIM(RTRIM(@x)) NOT LIKE '% [a-Z][a-Z]%' THEN 1 ELSE 0 END

For day of the month:

CASE WHEN @x LIKE '[12][0-9]' OR @x LIKE '0-9' OR @x LIKE '3[0-1]'

Others would be similar.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-07 : 14:19:19

Since you have these fields in Excel, try this:
http://www.macrostash.com/2011/10/08/simple-regular-expression-tutorial-for-excel-vba/

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-07 : 14:28:47

Thanks sunitabeck and DonAtWork!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources