String practice and translation

You know the core string functions and type casting. Now let us put them to work on real data: translating Portuguese product names to English, handling missing values with COALESCE, and combining everything into a clean output.

Let us put string skills to work by using the category translation table to convert Portuguese product names to English.

category_translation.sql
sql
-- Join with translation table to get English names
SELECT
  p.product_category_name AS portuguese,
  t.product_category_name_english AS english,
  COUNT(*) AS item_count
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN product_category_translation t
  ON p.product_category_name = t.product_category_name
GROUP BY p.product_category_name, t.product_category_name_english
ORDER BY item_count DESC
LIMIT 10;

The Olist dataset has a translation table for category names. LEFT JOIN ensures categories without translations still appear.

In PostgreSQL, TEXT and VARCHAR have identical performance. VARCHAR(n) adds a length check constraint, but internally they use the same storage. Most PostgreSQL developers just use TEXT. Only use VARCHAR(n) if you want the database to enforce a maximum length.

Fill in the blanks: Complete the string query

Loading practice…

Matching exercise: Match the string function

Loading practice…

One more essential for messy data: COALESCE. It takes a list of values and returns the first one that is not NULL. That makes it perfect for fallbacks, like showing the Portuguese category name whenever no English translation exists.

coalesce.sql
sql
-- COALESCE returns the first non-NULL argument
SELECT COALESCE(NULL, 'fallback');              -- 'fallback'
SELECT COALESCE(NULL, NULL, 'first', 'second'); -- 'first'

-- Fall back to the Portuguese name when no translation exists
SELECT
  p.product_category_name,
  COALESCE(t.product_category_name_english, p.product_category_name) AS display_name
FROM products p
LEFT JOIN product_category_translation t
  ON p.product_category_name = t.product_category_name
LIMIT 10;

COALESCE scans its arguments left to right and returns the first non-NULL value. Everything after the first match is ignored.

Quiz: Quiz

Loading practice…

Code playground: Clean product names

Loading practice…