?
Solved

mySQL Search Query: replace all non alphanumeric

Posted on 2009-04-21
4
Medium Priority
?
3,258 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:hankknight
  • 2
4 Comments
 
LVL 49

Expert Comment

by:Roonaan
ID: 24198553
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
 
LVL 16

Author Comment

by:hankknight
ID: 24198717
Thanks, but that is not the answer I am looking for.  Can this be done with regex?
0
 
LVL 14

Accepted Solution

by:
psadac earned 1200 total points
ID: 24198777
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
 
LVL 49

Assisted Solution

by:Roonaan
Roonaan earned 800 total points
ID: 24201685
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

862 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