SQL Query that ignores symbols

Question

I am looking for something that will only return alphanumerical characters in an SQL Query

Right now I have two coloums, ttitle and tsub where the ttitle could be Champ's Shack and the tsub could be "Grilled Cheese"

and I need it to return that field as champsshackgrilledcheese

So far I have gotten it down to champs'sshack"grilledcheese"

and I still need to remove those characters...

here is the code that gets it that far...

REPLACE(LOWER(CONCAT(TRIM(ttitle),TRIM(tsub))), ' ','')

What can I do to get it to return without symbols?? and only alphanumeric, I want to try and avoid SQL functions if possible

Are the single/double quotes the only symbols? If so, then you are already using `replace`, so why not just add to it to replace the single/double quotes? — Taryn, Feb 26 '13 at 22:55
I wanted to, however couldn't wrap my head around it, because I want to replace the symbol `'` but am using `'` in the actual code, I am prolly over thinking it — Chris James Champeau, Feb 26 '13 at 22:56
Read about **[escaping characters in string literals](http://dev.mysql.com/doc/refman/5.5/en/string-literals.html)** — ypercubeᵀᴹ, Feb 26 '13 at 22:57
@ChrisJamesChampeau to replace single quotes you will need to escape it `REPLACE(yourcol, '\'', '')` — Taryn, Feb 26 '13 at 22:58

score 2 · Accepted Answer · edited May 23 '17 at 12:21

2

REPLACE(REPLACE(REPLACE(LOWER(CONCAT(TRIM(ttitle),TRIM(tsub))), ' ',''),'\'',''),'"','')

Yes, this is extremely ugly. But since MySQL does not have support for regular expressions (unless you add an extension), you need a call to REPLACE for every single character you want to replace.

At least, if you want to avoid user-defined functions, as specified in your question.

edited May 23 '17 at 12:21

Community

1
1

answered Feb 26 '13 at 22:59

iblue

29,609
19
89
128

This is working however I am having an issue, I had escaped the value when it was added to the database so now I am getting this...`champs\sshack\grilledcheese\` because yes it is removing the 's and the "s but its not removing the \ and the manual http://dev.mysql.com/doc/refman/5.5/en/string-literals.html says to use \\ but that throws an error – Chris James Champeau Feb 26 '13 at 23:07
Try `\\ ` or if you are embedding your SQL in some other programming language, you maybe have to escape it twice: `\\\\ `. – iblue Feb 26 '13 at 23:10
wild escaping it twice worked...not sure why that is but thanks, the comments got me in the right direction, you just summed them up in an answer – Chris James Champeau Feb 26 '13 at 23:14

SQL Query that ignores symbols

1 Answers1