Searching for bad data in a varchar column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Searching for bad data in a varchar column

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-19 : 16:46:54

I need some help on this one:

I need to be able to find all records within a table that have bad data (application incorrectly inserts the data). This is a legacy application and one that I barely support, but I'll try to explain this as best that I can.

The table has a column that has the below format (where abc...mno is a 15 character alphanumeric parameter and 123 is a 3-digit parameter):
'ARPU:abcdefghijklmno ODOMETER: 123'

The bad data shows up in the last 3 characters of the column. The data is bad when the last 3 characters are not numbers, from 0-9. For example: 'ARPU:abcdefghijklmno ODOMETER: 3_Y' or 'ARPU:abcdefghijklmno ODOMETER: %09'

Let's pretend that the table is called Table1 and the column is called Column1. What would the query look like?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-19 : 16:58:29

maybe something like:

select * from
table1
where
column1 not like "%[0-9][0-9][0-9]"

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-19 : 16:59:35

or maybe this is faster:

where right(Column1,3) not like "[0-9][0-9][0-9]"

- Jeff

chadmat
The Chadinator

1974 Posts

Posted - 2003-03-19 : 17:04:41

create table #t1 (c1 varchar (5))
go
insert into #t1 values ('ab123')
insert into #t1 values ('ab12s')
insert into #t1 values ('ab123')
insert into #t1 values ('ab12a')
insert into #t1 values ('ab123')

select * from #t1
where substring(c1,3,3) not like '[0-9][0-9][0-9]'

-Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-19 : 17:06:19

Thank you! I went with Jeff's second option. I don't know why I can't dream this stuff up sometimes.

Tara

Edited by - tduggan on 03/19/2003 17:09:12

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-03-19 : 17:11:15

Or... (Using Chad's DDL)


SELECT * FROM #t1
WHERE ISNUMERIC(RIGHT(c1,3)) = 0

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 03/19/2003 17:14:06

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-19 : 17:19:42

David --

What if the right 3 characters are "0.0" or "-00" ??? I think the ISNUMERIC() function will not give you what you need in that case.

- Jeff

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-03-19 : 17:47:28

Jeff,

You are right.. It will validate all "number" types (Floating, decimals etc...)

Like my lecturer use to say... "Read the f$^%ing question!".

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources