Postgresql replace function

Question

When I do a select like this

select
replace(replace(t.field,'{0}',t.parameter01), '{1}', t.parameter02)
from table t

I obtain a null result even if t.field contain text without any placeholders. Can somebody explain why this happens?

"PostgreSQL 9.3.3"", edited: it's a t parameter, a column of type char with no value — Ion C
– Ion C, Commented Nov 28, 2014 at 10:19
Please show us a complete example including sample data (insert into...) — user330315
– user330315, Commented Nov 28, 2014 at 10:40

Joe Taras · Accepted Answer · 2014-11-28 10:56:31Z

1

I already figured out. If the third parameter of replace function is null, the result will be null although the second one doesn't match anything in the first one. An workaround is to replace the value of third parameter if it is null:

select 
replace(t.field, t.parameter01, coalesce(t.parameter02, ''))
from table t

edited Nov 28, 2014 at 10:56

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

answered Nov 28, 2014 at 10:54

Ion C

3233 gold badges13 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Postgresql replace function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related