• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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
0
kenuk110
Asked:
kenuk110
  • 4
  • 4
  • 2
1 Solution
 
kenuk110Author Commented:
By the way, the table is called nslDCS and the database is called T1.
0
 
sachinpatil10dCommented:
Try this

delete nslDCS where isnumeric(TREF)=0 or not len(TREF)=4

Open in new window

0
 
sachinpatil10dCommented:
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 rollback

Open in new window

0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
kenuk110Author Commented:
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.

0
 
ThomasianCommented:
DELETE nslDCS
WHERE TREF NOT LIKE '[0-9][0-9][0-9][0-9]'

Open in new window

0
 
ThomasianCommented:
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
*/

Open in new window

0
 
kenuk110Author Commented:
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.

0
 
sachinpatil10dCommented:
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
0
 
sachinpatil10dCommented:
you can go with this method also

-- will remove the spaces from '1234               ' and the output will be '1234'
update nslDCS set TREF= ltrim(rtrim(TREF))
--and then run the delete script 
delete nslDCS where isnumeric(TREF)=0 or not len(TREF)=4

Open in new window

0
 
kenuk110Author Commented:
Brilliant,

That has sorted out my problem perfectly, really appreciate your help.

Best Regards,

Ken
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now