Solved

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

Posted on 2014-11-25
8
225 Views
Last Modified: 2014-12-07
Hi All,

As the title suggests, I am looking to create a SELECT query that has a WHERE clause to display only companies that only contains letters, numbers, single spaces and no leading/trailing space


SELECT * FROM MyTable WHERE company  NOT LIKE '%  %' etc...


Many thanks
D
0
Comment
Question by:detox1978
  • 5
  • 2
8 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40464185
No leading spaces:
SELECT * FROM MyTable WHERE company  NOT LIKE '  %'

Open in new window

0
 
LVL 2

Author Comment

by:detox1978
ID: 40464202
Any thoughts on the only letters and numbers?

Obviously, No Trailing spaces would be.
SELECT * FROM MyTable WHERE company  NOT LIKE '% '

Open in new window


Also, I need it to be a single query
0
 
LVL 2

Author Comment

by:detox1978
ID: 40464229
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
0
 
LVL 2

Accepted Solution

by:
detox1978 earned 0 total points
ID: 40464234
I suspect this is close

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

Open in new window

0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40464278
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.
0
 
LVL 2

Author Comment

by:detox1978
ID: 40464291
HI Ray,

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

Expert Comment

by:Vitor Montalvão
ID: 40464305
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)<>' '

Open 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).
0
 
LVL 2

Author Closing Comment

by:detox1978
ID: 40485347
Regex I found is the closest.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now