mySQL Search Query: replace all non alphanumeric

I use the following mySQL query to find fields regardless of spaces.

Now I want to do this for ALL non alphanumeric characters, not just spaces.  So things like ' and " and _ should be replaced as well.
SELECT 
            `name`, `description`, `p_street`, `p_city`
      FROM 
            `organizations`
      WHERE
            replace(`name`, ' ', '') LIKE '%elloworl%'

Open in new window

LVL 16
hankknightAsked:
Who is Participating?
 
psadacConnect With a Mentor Commented:
since we don't have REGEXP_REPLACE(), it's a bit difficult, but using REGEXP and knowing that a non alphanumeric character can be matched by [^[:alnum:]], you have :
SELECT
    `name`, `description`, `p_street`, `p_city`
FROM
    `organizations`
WHERE
    `name` REGEXP 'e[^[:alnum:]]*l[^[:alnum:]]*l[^[:alnum:]]*o[^[:alnum:]]*w[^[:alnum:]]*o[^[:alnum:]]*r[^[:alnum:]]*l'

Open in new window

0
 
RoonaanCommented:
Hi,

Have you looked into using mysql's fulltext search capabilities?

Have you looked into commercial, opensource or other search engines?

This might save you loads of time.

Kind regards

Arnoud
0
 
hankknightAuthor Commented:
Thanks, but that is not the answer I am looking for.  Can this be done with regex?
0
 
RoonaanConnect With a Mentor Commented:
You then might want to create a spaceless field which you fill when you update a record, then search on that single field:

UPDATE table SET name = "hello world", description = "Dinky doodle", spacelessandlowercase ="hellowworld dinkydoodle"

Then use a ordinary like %elloworld%

Regards

-r-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.