Solved

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

Posted on 2014-11-25
8
231 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 47

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

773 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