SQL String Manipulation Functions

-- Basic string functions
SELECT
    UPPER('hello')                          AS upper,      -- HELLO
    LOWER('Hello')                          AS lower,      -- hello
    INITCAP('hello world')                  AS title,      -- Hello World
    LENGTH('hello')                         AS len,        -- 5
    TRIM('  hello  ')                       AS trimmed,    -- hello
    LTRIM('  hello')                        AS ltrimmed,
    RTRIM('hello  ')                        AS rtrimmed;

-- Substring and position
SELECT
    SUBSTRING('hello world' FROM 1 FOR 5)  AS sub,        -- hello
    LEFT('hello', 3)                        AS first3,     -- hel
    RIGHT('hello', 3)                       AS last3,      -- llo
    POSITION('world' IN 'hello world')      AS pos,        -- 7
    STRPOS('hello world', 'world')          AS pos2;       -- 7

-- Concatenation
SELECT
    'hello' || ' ' || 'world'              AS concat1,
    CONCAT('hello', ' ', 'world')          AS concat2,
    CONCAT_WS(', ', 'a', 'b', 'c')        AS joined;     -- a, b, c

-- Replace and translate
SELECT
    REPLACE('hello world', 'world', 'sql') AS replaced,
    TRANSLATE('12345', '135', 'abc')        AS translated; -- a2b4c

-- Pattern matching
SELECT
    'hello' LIKE 'hel%'                    AS like_match,
    'hello' ~ '^hel'                        AS regex_match,
    REGEXP_REPLACE('phone: 123-456', '[^0-9]', '', 'g') AS digits_only;

-- Split and aggregate
SELECT UNNEST(STRING_TO_ARRAY('a,b,c', ',')) AS item;
SELECT STRING_AGG(name, ', ' ORDER BY name) AS names FROM users;

-- Padding
SELECT
    LPAD('42', 6, '0') AS padded;          -- 000042