Remove special chars from field (calc field)

[TheWolf1]

Hello!

I would like to do something like:

SELECT title
FROM mytable
WHERE title LIKE '%some text condition%'

The field "title" can contain some special chars that I don't want. Those characters are:
:
!
’
,
?
$
.

I must strip those chars from "title" prior the WHERE...

Which mySql function can help me to achieve what I want? I will probably need to do a subquery right?

I hope i am clear enough :)

[Fumigator]

I would use the REPLACE() function.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

[TheWolf1]

I ended up using:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(post_title, '$', ''), '!', ''), '?', ''), ',', ''), '.', ''), ':', ''), '’', '') AS 'post_title'
FROM Wp_posts
WHERE post_status = 'publish'

is there something better?

[Fumigator]

There may be some better way... but hey if it 'aint broke don't fix it :D

I know in PHP you can use an array with the str_replace() function which would be ideal for this situation. But that doesn't really help you here.

[TheWolf1]

Yeah, I use str_replace() in PHP sometimes... I lilke the fact that it can takes arrays as params :)

Yeah my SQL works OK now. I was just concerned about it's optimisation since the query is runned for every link (<a href="">) on the page.

My final query is:

SELECT post_title
FROM
(

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(post_title, '$', ''), '!', ''), '?', ''), ',', ''), '.', ''), ':', ''), '’', '') AS 'post_title'
FROM Wp_posts
WHERE post_status = 'publish'

) tmp
WHERE post_title LIKE '%some PHP generated string%'

Like I said I can't only use PHP on this one since the DB field contains some characters that are not in my PHP variable...

(It's a WordPress i18n/SEO plugin that parse links on a page -from the URL- and show it's localized version).

[demtron]

To clean up the code and get rid of all the REPLACE statements, you might consider using regular expressions. Two resources that should help you out are:

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

http://www.brainbell.com/tutorials/MySQL/Using_MySQL_Regular_Expressions.htm

One caveat... I don't know whether regular expressions can be optimized within a query. Maybe nothing to worry about on a small source data set.

Anyway, regular expressions are extremely powerful and you'll likely find more applications for them in your coding once you begin using them

Hope that helps.