Solved

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

Posted on 2014-11-25
8
235 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 48

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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
 
LVL 109

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 48

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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.

828 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