3 replies to this topic

[Backslider]

I need to find valid records as part of a database cleanup script.
What I need is a regular expression that will match, for a single column:
records containing BOTH numbers AND letters (or any other character).
records containing ONLY numbers (including decimals)
Invalid records are those which contain ONLY letters and I do not want these returned, or NULL for the column.
Anybody good with regex?

[requinix]

Maybe I'm misreading it but your two conditions are simply "record contains numbers and other stuff OR record contains numbers and not other stuff". Which is really just saying that it has to contain a number.

[Backslider]

Hi, thanks for the reply. I actually solved this as follows:

SELECT model
FROM products
WHERE model !=  ''
AND model !=  '0'
AND (model REGEXP  '[0-9][a-z]' OR model REGEXP  '[0-9]')

[requinix]

Right. And what I'm saying is that model REGEXP "[0-9]" will also include the model!="" (contains something) and model REGEXP "[0-9][a-z]" (contains a number followed by a letter) cases. They're redundant. Still need the !=0 though.