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: 820
  • Last Modified:

MySQL: LIKE query for alphanumeric characters

I want a query for  MySQL that will return results irrespective of any characters except letters and numbers.

All these queries:

SELECT * FROM `XYZ` WHERE `QRS` LIKE '%hello-world%'
SELECT * FROM `XYZ` WHERE `QRS` LIKE '%helloworld%'
SELECT * FROM `XYZ` WHERE `QRS` LIKE '%hello world%'
SELECT * FROM `XYZ` WHERE `QRS` LIKE '%h e l l o w o r l d%'
SELECT * FROM `XYZ` WHERE `QRS` LIKE '%h-el-l-o-w-o-r-l-d%'
SELECT * FROM `XYZ` WHERE `QRS` LIKE '%hello**world%'

Should return matches for "Hellow/Orld"
0
hankknight
Asked:
hankknight
3 Solutions
 
Walter RitzelSenior Software EngineerCommented:
you can use RLIKE instead of LIKE. Then instead if the simple like pattern, you can use regular expressions.
Here is the link: http://dev.mysql.com/doc/refman/5.5/en/regexp.html
 
0
 
mrjoltcolaCommented:
Have you tried REGEXP?

For example, for HELLO variations:

SELECT ... WHERE COL REGEXP '.*[Hh].*?[Ee].*?[Ll].*?[Ll].*?[Oo].*'
0
 
hankknightAuthor Commented:
Thank you both for your comments.  It sounds like regular expressions are the way to go.

However, I don't know how to implement a working regular expression.

This does NOT work:


SELECT * FROM `XYZ` WHERE `QRS` RLIKE  'HelloWorld'

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
SELECT * FROM `XYZ` WHERE `QRS` RLIKE '^[\w]*$'

This will return lines where QRS column have only letters or numbers in it. If it has a space, that row will not return


Here is the link for the best reference:
http://www.regexlib.com/

You will learn in no time.
0
 
hackpinCommented:
this query should do.

if fieldname = Hellow/Orld
SELECT * FROM table WHERE fieldname LIKE '%/%'
or
if fieldname = 'hell(ow)orld'
try
SELECT * FROM table WHERE fieldname LIKE '%(%)%'
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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