How to use 'replace' function in PostgreSQL to string replace

Question

I want to do string replace in some data in pgsql database column. I tried

CASE
    WHEN (po.siteid IS NOT NULL) THEN replace('po.html_content', 'abcd', 'xxx')
        ELSE pc.html_content
 END

I want to replace string in po.html_content column. But above code is not working. Query get the data in po.html_content column without replacing. Is my code is wrong or any idea...

You should show some the data that isn't being processed as you expect. The output of SELECT po.html_content, replace(po.html_content, 'abcd', 'xxx') FROM po showing some of the rows where it hasn't been replaced. — Edmund
– Edmund, Commented Sep 21, 2012 at 2:44

John Woo · Accepted Answer · 2012-09-20 05:44:10Z

5

Don't enclosed the column name in a single quote, in that case, it is not a column anymore but a regular string.

CASE
    WHEN (po.siteid IS NOT NULL) THEN replace(po.html_content, 'abcd', 'xxx')
    ELSE pc.html_content
END

answered Sep 20, 2012 at 5:44

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

8 Comments

John Woo Over a year ago

@miuranga can you post the whole query?

John Woo Over a year ago

@miuranga do you want to update the row or only select the row?

John Woo Over a year ago

@miuranga are you aware that REPLACE is case sensitive?

Miuranga Over a year ago

I think it is lower case. I get it from PGSQL doc postgresql.org/docs/9.1/static/functions-string.html

John Woo Over a year ago

@miuranga no, that's not what i mean. for example you have value of HELLO world, if you try to execute this: replace('HELLO world', 'hello', 'not') the result is nothing since HELLO is not the same as hello

|

Collectives™ on Stack Overflow

How to use 'replace' function in PostgreSQL to string replace

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related