Length and case
SELECT length('বাংলা'); -- 5 (characters, not bytes)
SELECT octet_length('বাংলা'); -- 15 (bytes)
SELECT upper('postgres'), lower('POSTGRES'), initcap('rahim uddin');
Unlike MySQL, length() in Postgres already counts characters, so Bengali and emoji text works as expected.
Joining and splitting
SELECT name_en || ' - ' || department FROM students;
SELECT concat(name_en, ' - ', department) FROM students; -- NULL-tolerant
SELECT concat_ws(', ', name_en, email, department); -- skips NULLs
SELECT substring('PostgreSQL' FROM 1 FOR 4); -- 'Post'
SELECT substr('PostgreSQL', 5); -- 'greSQL'
SELECT left('Bangladesh', 5), right('Bangladesh', 4);
SELECT split_part('rahim@example.com', '@', 2); -- 'example.com'
SELECT string_to_array('a,b,c', ','); -- {a,b,c}
SELECT array_to_string(ARRAY['a','b'], '-'); -- 'a-b'
Trimming and padding
SELECT trim(' hello '), ltrim(' hi'), rtrim('hi ');
SELECT trim(both 'x' from 'xxhelloxx'); -- 'hello'
SELECT lpad('7', 3, '0'); -- '007'
SELECT rpad('CSE', 6, '-'); -- 'CSE---'
Searching and replacing
SELECT position('SQL' in 'PostgreSQL'); -- 8
SELECT strpos('PostgreSQL', 'gre'); -- 5
SELECT replace('2026-09-17', '-', '/');
SELECT reverse('abc'), repeat('ab', 3);
SELECT starts_with('PostgreSQL', 'Post');
Regular expressions
Postgres has first-class regex support, which is a genuine advantage over most databases:
SELECT 'rahim@example.com' ~ '^[a-z]+@[a-z]+\.com$'; -- boolean match
SELECT regexp_replace('Phone: 017-1234', '[^0-9]', '', 'g'); -- keep digits
SELECT regexp_match('CSE-101', '([A-Z]+)-([0-9]+)'); -- {CSE,101}
SELECT regexp_split_to_array('a1b2c3', '[0-9]');
SELECT * FROM regexp_split_to_table('one two three', '\s+');
Formatting
SELECT format('Hello %s, you are %s years old', name_en, age) FROM students;
SELECT to_char(1234567.891, 'FM999,999,999.00'); -- '1,234,567.89'
SELECT to_char(now(), 'DD Mon YYYY HH24:MI');
SELECT md5('text'), encode(sha256('text'), 'hex');
format() with %I (identifier) and %L (literal) is the safe way to build dynamic SQL inside functions.
Practical examples
-- mask an email
SELECT left(email, 2) || '****@' || split_part(email, '@', 2) FROM students;
-- normalise messy input
SELECT * FROM students WHERE lower(trim(email)) = 'rahim@example.com';
-- build a URL slug
SELECT lower(regexp_replace(title, '[^a-zA-Z0-9]+', '-', 'g')) AS slug FROM posts;
Remember that wrapping a column in a function blocks a plain index. Either create an expression index (
CREATE INDEX … ON students (lower(email))) or use thecitexttype for case-insensitive columns.