40 String and Numeric Functions

PostgreSQL ships with hundreds of built-in functions. The string and math families cover most day-to-day needs: case conversion, padding, trimming, splitting, replacing, hashing; rounding, absolute value, modulus, exponentiation, random. Get comfortable with the common ones and you will reach for them constantly.

What you’ll learn

• Common string functions: upper, lower, length, substring, position, trim, replace, split_part
• String concatenation with || and concat
• Regex with ~, ~*, regexp_replace, regexp_match
• Common numeric functions: abs, round, ceil, floor, mod, power, random
• The \df meta-command to discover more

Example

-- Case and length
SELECT upper('hello') AS u, lower('HELLO') AS l, length('hello') AS len;

-- Substring: SQL syntax and function syntax
SELECT substring('postgresql' FROM 1 FOR 8) AS sql_form,
       substring('postgresql', 1, 8)        AS fn_form;

-- Position: 1-based index, 0 if not found
SELECT position('gre' IN 'postgresql') AS pos;

-- Trim and replace
SELECT trim('  hi  ')                         AS trimmed,
       trim(both '/' FROM '/path/to/file/')   AS path_trim,
       replace('a-b-c', '-', '.')             AS replaced;

-- Splitting
SELECT split_part('a.b.c.d', '.', 2)                  AS second_part,
       string_to_array('a,b,c', ',')                  AS as_array;

-- Concatenation: || vs concat. concat tolerates NULL; || does not.
SELECT 'a' || NULL AS pipe_null,
       concat('a', NULL, 'b') AS concat_null;

-- Regex: ~ matches, ~* is case-insensitive
SELECT 'PostgreSQL' ~ 'gres' AS match,
       'PostgreSQL' ~* 'POSTGRE' AS imatch,
       regexp_replace('a1b2c3', '[0-9]', '#', 'g') AS no_digits;

-- Numeric: abs, round, ceil, floor, mod, power, random
SELECT abs(-5)                AS a,
       round(3.14159, 2)      AS r,
       ceil(2.1)              AS c,
       floor(2.9)             AS f,
       mod(10, 3)             AS m,
       power(2, 10)           AS p,
       round((random() * 100)::numeric, 2) AS rand;

substring has two forms: the SQL-standard substring(str FROM start FOR n) and the function-call style substring(str, start, n). Both are 1-based.

concat and || differ on NULL: 'a' || NULL is NULL (any operand makes the whole expression NULL), while concat('a', NULL, 'b') returns 'ab' — concat treats NULLs as empty strings. Pick the behavior you want explicitly.

The regex operators ~ and ~* are matching shortcuts. regexp_replace(text, pattern, replacement [, flags]) is the workhorse for substitutions; the 'g' flag means “replace all matches” (default is first only). regexp_match returns the captured groups.

Numeric functions are mostly self-explanatory. random() returns a double precision in [0, 1); use floor(random() * n)::integer for integers from 0 through n − 1. A direct cast rounds instead of truncating. For cryptographically secure random, use gen_random_bytes from pgcrypto.

Run the example

To run:

$ psql -f source/string-and-numeric-functions.sql postgres
   u   |   l   | len
-------+-------+-----
 HELLO | hello |   5
(1 row)
 ...

Common pitfalls

• Confusing substring indexing with zero-based languages — PostgreSQL is 1-based.
• replace is a literal-string replacement, not a regex. For regex, use regexp_replace.
• random() is not cryptographically secure. For security-sensitive cases, use pgcrypto’s gen_random_bytes.
• length counts characters; octet_length counts bytes. They differ for multibyte text (UTF-8).
• round(numeric) rounds ties away from zero: round(0.5) is 1 and round(-0.5) is -1. round(double precision) commonly rounds ties to even, with platform-dependent behavior.

Downloads

SQL file: string-and-numeric-functions.sql · All lesson files · Setup guide