Extracting number of specific length from a string in Postgres

Question

I am trying to extract a set of numbers from comments like

"on april-17 transactions numbers are 12345 / 56789"
"on april-18 transactions numbers are 56789"
"on may-19 no transactions"

Which are stored in a column called "com" in table comments

My requirement is to get the numbers of specific length. In this case length of 5, so 12345 and 56789 from the above string separately, It is possible to to have 0 five digit number or more more than 2 five digit number.

I tried using regexp_replace with the following result, I am trying the find a efficient regex or other method to achieve it

select regexp_replace(com, '[^0-9]',' ', 'g') from comments;

                      regexp_replace                   
----------------------------------------------------
          17                          12345   56789

I expect the result to get only

column1 | column2
12345     56789

Are there always 2 and only 2 numbers which would be the matching case? Or, could there be just 1 or 3 five digit numbers? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 24, 2019 at 7:37
Yes it possible to have more, 0 five digit number or more than 2 five digit number — Maverick
– Maverick, Commented Jul 24, 2019 at 7:40
The problem is that you cannot create a dynamic numbers of columns in an easy way. You need to know how many columns you would get. — S-Man
– S-Man, Commented Jul 24, 2019 at 7:48
Gave an solution for exact two columns. It is not possible to get one column at one try and two at the second. If we limit it two, always two columns are shown even if you find only one number. The second one would be NULL in this case. This is possible — S-Man
– S-Man, Commented Jul 24, 2019 at 8:00

S-Man · Accepted Answer · 2019-07-24 07:58:40Z

1

There is no easy way to create query which gets an arbitrary number of columns: It cannot create one column for one number and at the next try the query would give two.

For fixed two columns:

demo:db<>fiddle

SELECT 
   matches[1] AS col1,
   matches[2] AS col2
FROM ( 
    SELECT
        array_agg(regexp_matches[1]) AS matches
    FROM
        regexp_matches(
            'on april-17 transactions numbers are 12345 / 56789', 
            '\d{5}',
            'g'
        )
) s

regexp_matches() gives out all finds in one row per find
array_agg() puts all elements into one array
The array elements can be give out as separate columns.

edited Jul 24, 2019 at 7:58

answered Jul 24, 2019 at 7:50

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

2 Comments

Maverick Over a year ago

if those input comments are coming from table column the above SQL statement is not resulting with all the values

S-Man Over a year ago

@Maverick You should adjust your query of course. dbfiddle.uk/… ... This one holds the elements without any number: dbfiddle.uk/…

Collectives™ on Stack Overflow

Extracting number of specific length from a string in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related