Postgresql - How to get numeric value from the end of string and string without end numeric value

Question

I have the following usernames in Postgresql user table.

**username**
test
123test
123test456
test123
test45test
test55test55

So I am trying to get the string without end numeric value and end numeric value seperately.

I am expecting following output

str          num
----------------
test         0
123test      0
123test      456
test         123
test45test   0
test55test   55

How to write a Postgresql query that will return the above result?

GMB · Accepted Answer · 2019-11-29 17:09:47Z

1

You could use regexp_replace() and substring():

select
    regexp_replace(username, '\d+$', '') str,
    coalesce(substring(username from '\d+$')::int, 0) num
from mytable

Demo on DB Fiddlde:

with mytable as (
    select 'test' username
    union all select '123test'
    union all select '123test456'
    union all select 'test123'
    union all select 'test45test'
    union all select 'test55test55'
)
select
    regexp_replace(username, '\d+$', '') str,
    coalesce(substring(username from '\d+$')::int, 0) num
from mytable

str        | num
:--------- | --:
test       |   0
123test    |   0
123test    | 456
test       | 123
test45test |   0
test55test |  55

answered Nov 29, 2019 at 17:09

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Postgresql - How to get numeric value from the end of string and string without end numeric value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related