Selecting values of a string in PostgreSQL

Question

I have a Column which have string values in my PostgreSQL table.

The string looks like this:

'192.168.1.1-mike.landline,192.136.152-sam.phone,192.364.1.0-main-phone'

Based on the string how can I select the last most value before the comma starts. i.e. 192.364.1.0-main-phone

I tried to look it up but no luck so far

klin · Accepted Answer · 2018-04-02 20:23:21Z

1

Use the function regexp_replace().

select regexp_replace('192.168.1.1-mike.landline,192.136.152-sam.phone,192.364.1.0-main-phone', '.*,', '');

     regexp_replace     
------------------------
 192.364.1.0-main-phone
(1 row)

Read about POSIX Regular Expressions in the documentation.

Update. You can trim commas ending a string with the function rtrim(), e.g.:

select rtrim('some_string,', ',')

    rtrim    
-------------
 some_string
(1 row)

so your query may look like this:

select regexp_replace(rtrim(the_string, ','), '.*,', '');

edited Apr 2, 2018 at 20:23

answered Apr 2, 2018 at 20:11

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

sach jot Over a year ago

Thank you @klin for answering my question. Can you please help me with one more problem. The problem is that in some of my data my strings ends with a comma for an example '192.364.1.0-main-phone,'. How can I select the last value in that case

Collectives™ on Stack Overflow

Selecting values of a string in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related