Part 102: Advanced String Manipulation: REPLACE, LPAD, RPAD & STRPOS

Formatting identifiers, serial numbers, and searching within text strings.

1. Padding Serial Numbers with LPAD

-- Format order numbers with leading zeros (e.g. '000042'):
SELECT LPAD(CAST(id AS VARCHAR), 6, '0') AS formatted_invoice_no
FROM invoices;

2. String Replacement with REPLACE

-- Sanitize phone numbers:
SELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS digits_only
FROM contacts;

Key Takeaways

• Use LPAD and RPAD for fixed-width serial numbering.

In Part 103, we explore Mathematical Functions: ROUND, CEIL, FLOOR, MOD & POWER!