sqlpostgresqlregex-recursion

Extracting number of specific length from a string in Postgres


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

Solution

  • 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
    
    1. regexp_matches() gives out all finds in one row per find
    2. array_agg() puts all elements into one array
    3. The array elements can be give out as separate columns.