Create new table values based on partial column value - access

Question

I am trying to remove a ton of unnecessary rows in this table.

Currently there are 1.3m of them which are all an individual transaction id - with the country and destination assigned for each which is wayy wrong, given that the actual flight id is contained within the transaction id string.

The worst thing in the dataset which I've inherited, is the fact that within the transactions table there is a column with the extracted flight id as if it were a foreign key, but above is the 'flights' table. 'eyeroll'

My question is, how do I extract just the part which I have coloured in red? I can't use Left, Mid or Right, because they are variable lengths, some with 3 and 4 digits in the flight id itsself.

Is there a regex or something which can extract the text situated between 2 sets of underscores? which is just about the only viable rule in this sense.

Please bare in mind that I have never used regex before, and would need advice on how to format the expression in my query design view.

Any help is appreciated.

Answer 1

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