Postgres regex to uppercase

Question

How can I use regex in Postgres to replace a capture with an upper case version of itself.

regexp_replace(pf.description, '^(.)(.*)$', '\U\1\E\2', 'gi') as description

is giving me the string back with the literal values \U and \E.

possible duplicate of Replacing regex matched text with uppercase version in Postgresql — elixenide
– elixenide, Commented Jun 30, 2014 at 19:40
Are you using an outdated version of Postgres or why do you escape literal characters? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 30, 2014 at 19:46
@ErwinBrandstetter \U and \L are how I change case using regex. I am just trying to make the first letter of the string upper case. — 1252748
– 1252748, Commented Jun 30, 2014 at 19:55

Erwin Brandstetter · Accepted Answer · 2023-03-13 23:23:51Z

4

There is no built-in regex functionality in Postgres to convert to upper / lower case (that I'd know of).

SELECT upper(left('test_string', 1))
    || lower(right('test_string', -1));

Result:

Test_string

answered Jun 30, 2014 at 19:40

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Works nicely. Thanks much.