A little SQL help needed

[rstevenson]

While working on my test site I inadvertently uploaded some products with the wrong model numbers. All my model numbers should be 3 digits, even those that are less than 3 significant digits. For example, model number should be 003 rather than 3. My text file had been modified in another app that didn't keep the zeros, and I didn't notice in time.

So now I'd like to delete all products whose model number is less than 3 digits in length. In other words, I need a little grep inside the SQL statement. Since my grep skills are about equal to my SQL skills, and neither are up to the task, I wonder if some Zen guru could help.

TIA

Rob

[TheOracle]

Rather than deleting these fields, why not initiate a batch rename command from your phpmyadmin within the SQL query window ?

[rstevenson]

Because the upload also included the correctly modelled products, so the ones with the wrongly formatted model number are redundant. I should have mentioned that.

Rob

[TMC_Sherpa]

well, I'd check to be sure first,

run

SELECT * FROM `YOUR PREFIX HEREproducts`
WHERE (`products_model` REGEXP '^..$' OR
`products_model` REGEXP '^.$');

from my phpadmin and make sure you only get back what you want,
this will only show stuff with 1 or 2 digit model#....assuming thats what you want
I'm not sure how you would get the stuff out of
products_description

changing SELECT to DELETE will nuke them. did I say make a backup yet?
Hm... shoulda said that first....

[rstevenson]

Hi,

^.$ and ^..$

That's the part I didn't know. Neat!

Now if only I hadn't gotten impatient and deleted them manually. <:-)

Rob

[TMC_Sherpa]

Doh! Sorry about that, just found the thread when I posted.

^ <- beginning of word
. <- wildcard character
$ <- end of word


so ^..$ is only match things 2 chars long
^.$ one char

Hm, I guess you could use LIKE "__" or LIKE "_" also......
man I rate regular expressions, they make my head hurt