?
Solved

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

Posted on 2003-10-22
15
Medium Priority
?
214 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

864 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