Delete records that don't meet certain structure
Posted on 2011-10-22
I have a table that has information entered in to it from some form reading technology. The records 'should' be verified prior to being exported to the database BUT on a few occations this doesn't happen and bad data gets placed in the database. The root cause analysis here is to obvioulsy stop this before it gets to the database and we are working to get this to happen but in the interim I need a way to delete records in a table that don't meet the following:
Our TREF field should ONLY contain NUMBERS and they should always be FOUR digits in length, so: TREF 1234, TREF 5678 for instance.
What is happening is that we have records that have odd characters within them, so:
TREF 12'4, TREF ^678 as examples.
Is there anyway to delete anything in this table that doesn't conform to this?
Any help would be much appreciated.