...

View Full Version : delete rows not matching certain criteria



sanketgroup
10-08-2011, 05:01 PM
Is there anyway way to delete those rows not matching certain criteria ?
For example, required rows in Column A - always have either xxxxx-x formatted Account# or Date or word begins with "Total :" anything other then this- those rows are not useful and i want to automatically delete all those unwanted rows.
Delete the the rows which has value other then xxxxx-x formatted account# or date or word "Total :"
[Account# could be xxxxx-x or xxxx-x or xxx-x or xx-x or x-x means 5digit-1digit or 4digit-1digit or 3digit-1digit or 2digit-1digit or 1digit-1digit
Code is required in VB.NET and data is MYSQL
Thanx a lot

====INPUT====
Column A
56981-3
1459-3
10/25/2010
69655-9
Apple
Orange
Total:
01/02/2009
Paint1
987-1
Yellow
Total:

====OUTPUT===
Column A
56981-3
1459-3
10/25/2010
69655-9
Total:
01/02/2009
987-1
Total:

bobleny
10-10-2011, 10:28 PM
You should be able to just use a mysql regEx -> http://dev.mysql.com/doc/refman/5.1/en/regexp.html

You would end up with something like:


DELETE FROM `table` WHERE 'string' NOT REGEXP '^[0-9]{1,5}-[0-9]{1}$' AND 'string' NOT REGEXP '^Total:' AND ...


You could also get all of the data from the database and loop through it using VB's regEx. Just make a note of which row is failing or passing the regEx, and delete whichever you'd like.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum