kenuk110
asked on
Delete records that don't meet certain structure
Hi,
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.
Regards,
Ken
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.
Regards,
Ken
Try this
delete nslDCS where isnumeric(TREF)=0 or not len(TREF)=4
use begintrans commit tran or take backup before you carry this process
begin tran
delete nslDCS where isnumeric(TREF)=0 or not len(TREF)=4
select * from nslDCS
-- check if expected out put is seen then commit or rollbackASKER
Hi,
I'll try this, just backing up the table first. I have looked at the data and even though MOST are 4 numerics, some have four numerics but a row of spaces after them. Will this delete those even though the first four are digits? Does this make sense?
So TREF '1234' is in there and also '1234 ' as an example? I don't know why some come in with extra spaces after them! Maybe it needs to 'truncate' this field before it does the delete? I'm just worried the data won't be four digits on a lot of the records and it goes and deletes the lot - oops.
I'll try this, just backing up the table first. I have looked at the data and even though MOST are 4 numerics, some have four numerics but a row of spaces after them. Will this delete those even though the first four are digits? Does this make sense?
So TREF '1234' is in there and also '1234 ' as an example? I don't know why some come in with extra spaces after them! Maybe it needs to 'truncate' this field before it does the delete? I'm just worried the data won't be four digits on a lot of the records and it goes and deletes the lot - oops.
DELETE nslDCS
WHERE TREF NOT LIKE '[0-9][0-9][0-9][0-9]'
Here's a test script to check if it works on your data
DECLARE @nslDCS TABLE (TREF varchar(100))
INSERT @nslDCS
SELECT '124'
UNION ALL SELECT '9527'
UNION ALL SELECT '1233 '
UNION ALL SELECT '1.31'
UNION ALL SELECT '1^31'
DELETE @nslDCS WHERE TREF NOT LIKE '[0-9][0-9][0-9][0-9]'
SELECT * FROM @nslDCS
/*Result:
TREF
9527
1233
*/ASKER
Hi Thomasian,
That command wiped out my table, it was a backup though. I guess the tref column uncludes spaces on all of the fields. I had tried sachinpatil10d prior to yours and it left all the correct values and only deleted the ones that either had alien characters or were not 4 digits in length.
sachinpatil10d,
The trans part of the script, the last bit you wrote, does this do something extra than the first script, I understand the transactional part, but the last commented part, do I need to do anything?
I'm not a DBA so sorry for the stupid questions.
That command wiped out my table, it was a backup though. I guess the tref column uncludes spaces on all of the fields. I had tried sachinpatil10d prior to yours and it left all the correct values and only deleted the ones that either had alien characters or were not 4 digits in length.
sachinpatil10d,
The trans part of the script, the last bit you wrote, does this do something extra than the first script, I understand the transactional part, but the last commented part, do I need to do anything?
I'm not a DBA so sorry for the stupid questions.
if the script is running ok for you
run only the single delete line
it was only for precaution if any thing went wrong
you can carry further with the single line delete query
run only the single delete line
it was only for precaution if any thing went wrong
you can carry further with the single line delete query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant,
That has sorted out my problem perfectly, really appreciate your help.
Best Regards,
Ken
That has sorted out my problem perfectly, really appreciate your help.
Best Regards,
Ken
ASKER