PostgreSQL Regex – Match All Digits in Column

That was an issue with the regex syntax. To get the desired word, you will have to group it under ()

            SELECT
                regexp_matches(
                    'select :barns, :fowl, :cows from farm',
                    '(:\w+)',
                    'g'
                )

Since your starting position is fixed (after the 2nd _), you can use an Instr() function to find the position of the 3rd _, and just take the characters between.

Instr() returns the postion of the first instance of a character in a string. If the character is not found, it'll return 0. Note, Access considers the first character position to be 1.

Give this a try:

SELECT Mid(txn_id, 12, InStr(12, txn_id, '_')-12) 
FROM MyTable`

Just for reference: https://support.office.com/en-us/article/InStr-Function-85d3392c-3b1c-4232-bb18-77cd0cb8a55b

Edit:

This is pretty gross, but if the 2nd _ position was also variable, this should work:

SELECT Mid(txn_id
           , Instr(Instr(1, txn_id, '_')+1, txn_id, '_')+1
           , Instr(
                   Instr(
                         Instr(1, txn_id, '_')+1
                         , txn_id
                         , '_')+1
                   , txn_id
                   , '_')-1 -  Instr(Instr(1, txn_id, '_')+1, txn_id, '_')
            )
FROM MyTable