How to use an array in REPLACE() function?

Question

I have some characters that I want replace them with some other characters.

- => *
/ => .
< => {
> => }

Also here is an example of a record in the table: (database)

+-------------------+
| - it <is> a test/ |
+-------------------+

And I want this output:

+-------------------+
| * it {is} a test. |
+-------------------+

Well, I can do that using 4 separated update queries: (through execute them one after the other)

UPDATE table SET `col` = REPLACE(`col`, '-', '*' );
UPDATE table SET `col` = REPLACE(`col`, '/', '.' );
UPDATE table SET `col` = REPLACE(`col`, '<', '{' );
UPDATE table SET `col` = REPLACE(`col`, '>', '}' );

Now I want to know, is there any shorter solution? (because in reality, there is more than 12 cases for replacing). For example, is it possible to I write all those symbol in an array and then put that array in the update query just for one time?

marc_s · Accepted Answer · 2016-03-09 06:38:24Z

4

UPDATE table 
SET `col` = REPLACE(REPLACE(REPLACE(REPLACE(`col`, 
                                                         '>', 
                                                         '}' ), 
                                                 '<', 
                                                 '{' ), 
                                         '/', 
                                         '.' ), 
                                 '-', 
                                 '*' );

There is a function on postgresql translate to do exactly that. http://www.postgresql.org/docs/9.1/static/functions-string.html

Here I found a stored procedure replicating that function, (comment are in spanish).

http://www.forosdelweb.com/f86/funcion-translate-mysql-704190/

edited Mar 9, 2016 at 6:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 16, 2015 at 14:26

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2366842 Over a year ago

Not to say that's not a good solution, but it should be noted that you're obviously sacrificing readability for efficiency with going this route.

glglgl Over a year ago

Looks ugly, but can easily be generted in a programmatical way.

Collectives™ on Stack Overflow

How to use an array in REPLACE() function?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related