String Functions Deep Dive
String Functions Deep Dive — Advanced string manipulation with CONCAT, SUBSTRING, REPLACE, REGEXP, TRANSLATE, and more. The guide walks through CONCAT() & String Concatenation, SUBSTRING() & String Extraction, REPLACE() & TRANSLATE(), Pattern Matching with REGEXP, Padding & Trimming, Splitting & Position Functions. CONCAT() joins strings safely — it converts NULLs to empty strings. Syntax: CONCAT(first_name, ' ', last_name). The || operator (SQL standard, PostgreSQL) returns NULL if any operand is NULL. SQL Server uses + for concatenation. CONCAT_WS (MySQL/PostgreSQL) adds a separator: CONCAT_WS(', ', col1, col2, col3). Use CONCAT_WS to avoid trailing separators. SUBSTRING(str, start, length) extracts a portion of a string. In SQL, positions start at 1, not 0. SUBSTRING('Hello World', 7, 5) returns 'World'. LEFT(str, n) and RIGHT(str, n) extract from the start or end. PostgreSQL also supports SUBSTRING(str FROM pattern) with POSIX regex. Use SUBSTRING for parsing fixed-width fields and codes. The guide is organized into 6 sections that build on each other, each pairing a prose explanation with real SQL you can run as-is.