Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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

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
detox1978
Asked:
detox1978
  • 5
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No leading spaces:
SELECT * FROM MyTable WHERE company  NOT LIKE '  %'

Open in new window

0
 
detox1978Author Commented:
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
 
detox1978Author Commented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
detox1978Author Commented:
I suspect this is close

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

Open in new window

0
 
Ray PaseurCommented:
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
 
detox1978Author Commented:
HI Ray,

I found that on the web, I dont really understand it.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
detox1978Author Commented:
Regex I found is the closest.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now