Solved

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

Posted on 2014-11-25
8
229 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 46

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 46

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

867 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

17 Experts available now in Live!

Get 1:1 Help Now