String functions and type casting

Real-world data is messy. City names in different cases, categories in Portuguese, zip codes stored as text. String functions and type casting let you clean, transform, and convert data right in your query.

It depends on the use case. For ad-hoc analysis and reporting, cleaning in SQL is faster and keeps everything in one place. For application logic, you might clean in code. For ETL pipelines, SQL is usually preferred because it processes data where it lives.

string_functions.sql
sql
-- Basic string functions
SELECT
  product_category_name,
  UPPER(product_category_name) AS upper_name,
  LOWER(product_category_name) AS lower_name,
  LENGTH(product_category_name) AS name_length,
  TRIM(product_category_name) AS trimmed
FROM products
WHERE product_category_name IS NOT NULL
LIMIT 10;

UPPER/LOWER change case. LENGTH counts characters. TRIM removes leading/trailing whitespace.

Beyond case changes, you often need to combine strings together or extract portions of them. CONCAT and SUBSTRING handle these tasks.

concat_substring.sql
sql
-- CONCAT and SUBSTRING
SELECT
  customer_id,
  CONCAT(customer_city, ', ', customer_state) AS location,
  SUBSTRING(customer_zip_code_prefix FROM 1 FOR 3) AS zip_prefix_3,
  REPLACE(customer_city, 'sao paulo', 'SP Capital') AS city_clean
FROM customers
LIMIT 10;

CONCAT joins strings. SUBSTRING extracts a portion. REPLACE swaps text within a string.

Sometimes data is stored as one type but you need it as another. Type casting converts between types like text to integer or timestamp to date.

type_casting.sql
sql
-- Type casting: PostgreSQL :: syntax
SELECT
  price::INTEGER AS price_rounded,
  order_purchase_timestamp::DATE AS order_date,
  CAST(freight_value AS INTEGER) AS freight_int
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
LIMIT 10;

-- Useful for rounding and date extraction
SELECT
  ROUND(AVG(price)::NUMERIC, 2) AS avg_price
FROM order_items;

The :: operator casts between types. CAST(x AS type) is the standard SQL equivalent. Both work in PostgreSQL.

Quiz: Quiz

Loading practice…