Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-10-22
15
Medium Priority
?
211 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 70

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 70

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 70

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 70

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 70

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 70

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 70

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 70

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

618 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