Solved

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

Posted on 2003-10-22
15
203 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:BernieTay
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9601774
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9601797
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
 

Author Comment

by:BernieTay
ID: 9601939
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
 
LVL 6

Expert Comment

by:lausz
ID: 9601947
You can run a vbscript before to import and discard the problematic records
0
 

Author Comment

by:BernieTay
ID: 9601974
Thanks lausz but I want to do this the die hard way if possible, and keep it all SQL server
0
 

Expert Comment

by:Pendulumman
ID: 9602087
How about doing this:

Select * from CustomerWork where not IsNumeric(F1) <> 1
0
 

Expert Comment

by:Pendulumman
ID: 9602096
This is what I meant:

Select * from CustomerWork where IsNumeric(F1) <> 1
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9602198
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 300 total points
ID: 9602242
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9602289
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9602302
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
 

Author Comment

by:BernieTay
ID: 9602327
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9602338
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
 

Author Comment

by:BernieTay
ID: 9602358
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9602437
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now