Mysql replace function returns null

Question

I have a table activity_types which contains the text for activities e.g.
New project has been assigned to {user} with name {project}
New discussion has been posted by {client} on {project}
Project {project} has been closed by {user}
A draft has been approved by {client} on {project}
and so on

I have another table in which the id of these texts has been populated.

Now my requirement is to fetch these by replacing the text within {} with their actual values like {client} should be replaced with client name, {project} should be replaced with project name.

I have tried using nested replace mysql function but it returns null string. Here is my query

SELECT      REPLACE(REPLACE(REPLACE(att.type_text, '{project}', p.project_business_name), '{user}', u.user_fullname), '{client}', c.client_name) AS activity, a.*
FROM        activities a
LEFT JOIN   activity_types AS att ON att.type_id = a.activity_type
LEFT JOIN   users u ON u.user_id = a.activity_user AND a.activity_user IS NOT NULL
LEFT JOIN   projects p ON p.project_id = a.activity_project AND p.project_is_removed = '0'
LEFT JOIN   clients c ON c.client_id = a.activity_client AND a.activity_client IS NOT NULL;

The problem is that if i replace the c.client_name with static text it works fine but when i replace the static string with c.client_name it return null in activities which does not have {client}. Any guesses where am i doing wrong?

John Woo · Accepted Answer · 2012-10-22 15:06:57Z

1

try adding COALESCE on the column

...,COALESCE(c.client_name, ''),

basically if the column (c.client_name) is null, it will be replace with empty string.

what happens is that when part of the string is replace with null, the whole string becomes null.

answered Oct 22, 2012 at 15:06

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

Marc B · Accepted Answer · 2012-10-22 15:07:54Z

1

SQL null is contagious. Once you get a null value somewhere and use that null value in functions and derived values, the whole result becomes null. As such, if any of your fields in the REPLACE() chain are null, the whole result becomes null. You can try something like

COALESCE(name_of_field, '')
or
IFNULL(name_of_field, '')

to convert those nulls into an empty string.

answered Oct 22, 2012 at 15:07

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

Mysql replace function returns null

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related