I have two tables, “products” and “brands”. In the “products” table, the product’s name is in the “name” field. The product’s brand is represented by the # (diez) character. To know which is the brand, there’s a “brand_id” field.

So far we have:

products table:
  id: int
  name: varchar
  brand_id: int

brands table:
  id: int
  name: varchar

I was thinking in the other day, can I build the name only in SQL? The problem is that not all products have a brand…

The first approach was the REPLACE() function:

function REPLACE(str, from_str, to_str)

Nice, but… There’s always a but… In the documentation it doesn’t states, when to_str is NULL, the result is also NULL! Seems to me illogical, normally I expect the from_str
cleared from str in this case…

So, the query

SELECT REPLACE(name, '#', (SELECT name FROM brands WHERE id = products.brand_id)) FROM products;

results in several NULL rows… This is wrong, since products without brands should be displayed as-is.

What solves the problem, is the IF function:

IF(condition, true_value, false_value)

We use the “brand_id” field as condition, we replace the diez only if brand_id > 0. The final query looks like this:

SELECT IF(brand_id > 0, (REPLACE(name, '#', (SELECT name FROM brands WHERE id = products.brand_id))), name) FROM products;