# String and Numeric Functions

> Transform strings and use common numeric, rounding, and random-number functions.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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 {#example}

```sql {num="40.1" caption="String and Numeric Functions"}
-- 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 {#run}

To run:

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

## Common pitfalls {#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.

> [!TIP] Tip
> In interactive psql, `\df *upper*` lists every function with "upper" in the name. `\df+ upper` shows the source/owner. Excellent way to discover what is available.

> [!NOTE] Try it
> Build a label: `SELECT upper(name) || ' [' || length(name) || ']' FROM fruits;`. Round all averages: `SELECT customer_id, round(avg(total), 2) FROM orders_example GROUP BY customer_id;`. Use regex to find names starting with a vowel: `SELECT name FROM fruits WHERE name ~* '^[aeiou]';`.

## Downloads {#downloads}

SQL file: [string-and-numeric-functions.sql](/examples/source/string-and-numeric-functions.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)