Comparing alphanumeric only value of fields

[Tony Dillon]

Hi Everyone

 

I am trying to run a query that performs a SELECT based on the value of
a field that has been reduced to only it's alpha-numeric components.

 

For example, I would like to be able to match postcodes that contain the
values below using the comparison: ch1123

 

CH1 123                                ( with space )

Ch1 123,                               ( with space and lower case and
comma )

"cH1 123"                             ( space, quotes, case )

"cH1 123,"                           ( space, comma, case, quotes )

 

The postcode I'm checking is generated in PHP and so will be lowercase
alphanumeric characters: ch1123

 

I've tried using REGEXP like this:

 

SELECT postcode FROM table WHERE ( postcode REGEXP '[^A-Za-z0-9]' ) =
"ch1123"

 

But I don't think this is the correct syntax, as REGEXP returns only 0
or 1.

 

Is there any way I tell mySql to process the "postcode" field into only
alphanumeric values before it does the comparison?

 

Many thanks,

T