?
Solved

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

Posted on 2003-10-22
15
Medium Priority
?
210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
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:Scott Pletcher
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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
 
LVL 69

Expert Comment

by:Scott Pletcher
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:
Scott Pletcher earned 1200 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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

765 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