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, Jul 24 '19 at 07:37
Yes it possible to have more, 0 five digit number or more than 2 five digit number — Maverick, Jul 24 '19 at 07: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, Jul 24 '19 at 07: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, Jul 24 '19 at 08:00

S-Man · Accepted Answer · 2019-07-24T07:58:40.973

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 '19 at 07:58

answered Jul 24 '19 at 07:50

S-Man

22,521
7
40
63

if those input comments are coming from table column the above SQL statement is not resulting with all the values – Maverick Jul 24 '19 at 08:28
@Maverick You should adjust your query of course. https://dbfiddle.uk/?rdbms=postgres_11&fiddle=65549ab18ba01b35fcce056f924c2b49 ... This one holds the elements without any number: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=eaf43d8ce4c40fd287b86ca8f9170ec1 – S-Man Jul 24 '19 at 08:31

Extracting number of specific length from a string in Postgres

1 Answers1