Select data from a field where it is not like data type

I have a holding table for customer data most of these fields are nvarchar.
Customers send us their data in csv format which gets written to the database, this sometimes gets corrupted, and I would like to be able to check for that.
For example field F1 which should contain the site id (4-5 numbers) could contain a letter where the file has been corrupted so instead of 1234 it could be 1234t
How can I do a search based on the data type; and then update the record that does not fall into the correct criteria.
E.g Search all of F1 where the data type can not be converted to int (also other different data types for each field dec, datetime etc)
Is this possible, and can an example be offered
BernieTayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
For non-char datatypes, SQL itself will check before loading the data.  For example, SQL won't allow an invalid date to be placed into a date field.

To check a char column for non-integer chars, this should work:

SELECT *
FROM customerWork
WHERE f1 LIKE '%[^0-9]%'
0
Scott PletcherSenior DBACommented:
As to how to update/correct that data, that depends on what you want to do.  Do you want to strip out the bad chars?  Replace them with another char?

Btw, is this SQL 7 or 2000?
0
BernieTayAuthor Commented:
SQL2K
The holding table field is nvarchar, due to the type and amount of data received from the customers, this has been left like this. The convert to int,dec,datetime is done when moving specific records to a different table (Where the fields are already declared as type int,dec etc).
What I want to do is select the data in the holding table that is not or can not be converted to an integer/dec/datetime type .


Each row has a unique id so I can probably update based on where the id is not in etc etc. I do not want to alter the data except to update the export flag to 9.

Where you have %[^0-9]% a little more explanation on what this does, can this also be aplied to dec and datetime.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lauszCommented:
You can run a vbscript before to import and discard the problematic records
0
BernieTayAuthor Commented:
Thanks lausz but I want to do this the die hard way if possible, and keep it all SQL server
0
PendulummanCommented:
How about doing this:

Select * from CustomerWork where not IsNumeric(F1) <> 1
0
PendulummanCommented:
This is what I meant:

Select * from CustomerWork where IsNumeric(F1) <> 1
0
Scott PletcherSenior DBACommented:
I suggest *not* using ISNUMERIC() because it has too many formats to accept so accepts a lot of what most would consider non-numeric data.  For example, ISNUMERIC() returns 1 for *all* of the following:

SELECT ISNUMERIC(','), ISNUMERIC('.'), ISNUMERIC('20d04'), ISNUMERIC('11e3')



Can you tell, based on another column, if you need to edit for integer, date, decimal, etc., or do you always need to check if the value matches *any* of those types.
0
Scott PletcherSenior DBACommented:
LIKE looks for a pattern of data.  The % means allow any number of characters.  The [] indicates a specific char(s) to check for; a dash indicates a range, so 0-9 is 0, 1, 2, 3, ..., 9.  For example:

LIKE '[0-9]%'  --match any value that starts with 0-9
LIKE '%[0-9]'  --match any value that end with 0-9
LIKE '%[0-9]%'  --match any value that has 0-9 anywhere in it

The ^ stands for "NOT".  So:
LIKE '%[^0-9]%'  --match any value that has a char that is not 0-9 anywhere in it

So, this finds any column with a non-integer char in the column.


For decimal data, just add a decimal point to the allowable values:

SELECT *
FROM customerWork
WHERE f1 LIKE '%[^.0-9]%'

This finds all invalid values.  To find the valid values, naturally remove the ^.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
To check for dates, you can use ISDATE, it doesn't have the problems of ISNUMERIC (hurray!).  So, to sum it up, to check for a valid decimal *or* valid date, do this:

SELECT *
FROM customerWork
WHERE f1 LIKE '%[.0-9]%' OR ISDATE(f1) = 1

To check for invalid values, of course do this:

SELECT *
FROM customerWork
WHERE f1 LIKE '%[^.0-9]%' OR ISDATE(f1) = 0


The decimal check is not 100% accurate -- there could be two (or more) decimal points in the data.  If you need to check for that also, you can do this:
SELECT *
FROM customerWork
WHERE f1 LIKE '%[.0-9]%' OR ISDATE(f1) = 1 AND LEN(f1) - LEN(REPLACE(f1, '.', '')) <= 1
0
Scott PletcherSenior DBACommented:
Oops, when mixing ANDs and ORs, should always use parentheses to be clear (especially in that specific case, since the OR will be evaluated first, which is not what we want):

SELECT *
FROM customerWork
WHERE (f1 LIKE '%[.0-9]%' OR ISDATE(f1) = 1) AND LEN(f1) - LEN(REPLACE(f1, '.', '')) <= 1
0
BernieTayAuthor Commented:
Yep
I can tell by the rectype field what type of data will be held in each column
E.g
RecType Sales *should* have F2 int F3 dec F4 date/time
RecType Dip  *should* have F2 dec F3 dec F4 date/time

Scott I am really warming to your answer
If you can tell me how to use your above explanation for the following the points (+50) are yours

2003-06-30 00:00:00.000
0
Scott PletcherSenior DBACommented:
Oops, that earlier invalid check:

SELECT *
FROM customerWork
WHERE f1 LIKE '%[^.0-9]%' OR ISDATE(f1) = 0

needs reworked, because that will always fail if it's not a date, but it could be a valid integer and not be a date.  So use this instead:

SELECT *
FROM customerWork
WHERE f1 LIKE '%[^.0-9]%' AND ISDATE(f1) = 0
0
BernieTayAuthor Commented:
Wow answered my next part before I got to post
Think this will get me out of a major jam
Points comming at ya :-)
0
Scott PletcherSenior DBACommented:
Just saw your post on the rec types -- great!  That allows something like this:

-- find INVALID data
SELECT RecType, ...keys..., f2, f3, f4, ...
FROM ...
WHERE 1 =
    CASE RecType
    WHEN 'Sales' THEN
        CASE WHEN f2 LIKE '%[^0-9]%' OR f3 LIKE '%[^.0-9]%' OR ISDATE(f4) = 0 THEN 1 ELSE 0 END
    WHEN 'Dip' THEN
        CASE WHEN f2 LIKE '%[^.0-9]%' OR f3 LIKE '%[^.0-9]%' OR ISDATE(f4) = 0 THEN 1 ELSE 0 END
    ELSE 0 END  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.