Working with String Functions
1. Concatenating Strings
| Form | NULL Handling |
|---|
| a || b | NULL if any operand is NULL |
| concat(a,b,...) | Skips NULL |
| concat_ws(sep, a, b, ...) | Uses separator, skips NULL |
SELECT first_name || ' ' || last_name AS full_name FROM users;
SELECT concat_ws(', ', city, state, country) FROM addresses;
2. Converting Case
| Function | Effect |
|---|
| upper(s) | Uppercase |
| lower(s) | Lowercase |
| initcap(s) | Title Case |
3. Trimming Whitespace
SELECT trim(' hi '),
ltrim(' hi'),
rtrim('hi '),
trim(BOTH 'x' FROM 'xxhixx');
4. Padding Strings
SELECT lpad('5', 3, '0'), -- '005'
rpad('hi', 5, '.'); -- 'hi...'
5. Finding Substrings
SELECT position('@' IN email) FROM users;
SELECT strpos('hello world','world'); -- 7
| Function | Returns |
|---|
| position(sub IN str) | 1-based index, 0 if missing |
| strpos(str, sub) | Same |
SELECT substring('abcdef' FROM 2 FOR 3); -- 'bcd'
SELECT substr('abcdef', 2, 3); -- 'bcd'
SELECT left('abcdef', 3), right('abcdef', 3);
7. Replacing Text
SELECT replace('a.b.c', '.', '-');
SELECT translate('a.b,c', '.,', '--');
SELECT overlay('Txt2' placing 'ext' from 2 for 1); -- 'Text2'
8. Splitting Strings
SELECT string_to_array('a,b,c', ','); -- {a,b,c}
SELECT split_part('a,b,c', ',', 2); -- 'b'
SELECT regexp_split_to_table('a,b;c', '[,;]'); -- 3 rows
9. Reversing Strings
SELECT reverse('abcdef'); -- 'fedcba'
10. Repeating Strings
SELECT repeat('ab', 3); -- 'ababab'
11. Calculating String Length
| Function | Returns |
|---|
| length(s) / char_length(s) | Characters |
| octet_length(s) | Bytes |
| bit_length(s) | Bits |
SELECT format('Hello %s, you have %s messages', name, n) FROM inbox;
EXECUTE format('SELECT * FROM %I WHERE id = $1', table_name) USING :id;
| Spec | Use |
|---|
| %s | Argument as string |
| %I | Quoted identifier (SQL-safe) |
| %L | Quoted literal (SQL-safe) |
13. Using Quote Functions
| Function | Purpose |
|---|
| quote_ident(s) | Safely quote identifier |
| quote_literal(s) | Safely quote literal |
| quote_nullable(s) | Like quote_literal but NULL → 'NULL' |