MySQL: WHERE field only contains letters, numbers, single spaces and no leading/trailing space

No leading spaces:

SELECT * FROM MyTable WHERE company  NOT LIKE '  %'

Select allOpen in new window

Any thoughts on the only letters and numbers?

Obviously, No Trailing spaces would be.

SELECT * FROM MyTable WHERE company  NOT LIKE '% '

Select allOpen in new window

Also, I need it to be a single query

I found this online.

SELECT * FROM MyTable WHERE Company REGEXP '^[A-Za-z0-9]+$'

So I need to add the single space and no leading/trailing space to it

Regular expression engines differ a little from each other; I think you're headed in the right direction with that pattern.  I would try what you have both with and without the plus sign.  Be aware that it will match a single blank, or multiple blanks, too.  You may want to do a "down-select" into a temporary table so you can use more than one REGEX to filter the data.

HI Ray,

I found that on the web, I dont really understand it.

detox, you are almost there but that solution miss the leading spaces. For that I added the following:

SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$' AND LEFT(column, 1)<>' '

Select allOpen in new window

RegExp --> Regular Expression - are the valid expressions that you want to find in a string. You can also NEGATE and then means that you don't want the strings with the presented expressions (NOT REGEXP).

Regex I found is the closest.