Postgres regexp_replace, remove "st", "nd", "rd"

I need remove above suffixes and keep just a number and other text.

The string for example:

Start from 1st, 2nd, 3rd

Should be

Start from 1, 2, 3

Solution

Use an or condition on the three possible values:

select regexp_replace(the_column, '(st)|(nd)|(rd)', '', 'g') as new_value
from the_table;

You might also want to use case insensitive replacing, by using the flags 'gi' instead of just 'g'.

If those string can appear for themselves (i.e. not following a number) you need to extend the regex:

regexp_replace(the_column, '([0-9])((st)|(nd)|(rd))', '\1', 'g')