[SOLVED] Adding regex match found in column to last column

Issue

Using perl, sed or awk in bash, I need to find regex patterns in csv column 3 and add it to last column 4.

For example, turn this:

2020/10/26,249.00,Test10028
2020/10/24,569.00,Test 00074
2020/10/24,1999.00,0016 1708fqb8ba
2020/10/24,1399.00,00450 165058hb1bda
2020/10/23,399.00,Att-10170 158bb8ba
2020/10/22,599.00,Ref:10150 1605fk0bsf
2020/10/22,5669.00,1605fk0bsf

Into this:

2020/10/26,249.00,Test10028,10028
2020/10/24,569.00,Test 00074,00074
2020/10/24,1999.00,0016 1708fqb8ba,0016
2020/10/24,1399.00,00450 165058hb1bda,00450
2020/10/23,399.00,Att-10170 158bb8ba,10170
2020/10/22,599.00,Ref:10150 1605fk0bsf,10150
2020/10/22,5669.00,1605fk0bsf,

The PCRE regex I found that filters what I need (Only numbers, length 4-5, starts with 0-1, no tailing words.):

(?<!\d)[0-1]\d{3,4}(?!\w)

Using this in akw or sed does not work. Some code I tried:

awk -F, 'match($3, /(?<!\d)[0-1]\d{3,4}(?!\d)/, a) {print a[0]","$4 }' file.csv
sed -re 's/(?<!\d)[0-1]\d{3,4}(?!\d)/g' file.csv

But perl does give me results using:

perl -pe 's/(?<!\d)[0-1]\d{3,4}(?!\w)/!!!!/g' file.csv
2020/10/26,249.00,Test!!!!
2020/10/24,569.00,Test !!!!
2020/10/24,!!!!.00,!!!! 1708fqb8ba
2020/10/24,!!!!.00,!!!! 165058hb1bda
2020/10/23,399.00,Att-!!!! 158bb8ba
2020/10/22,599.00,Ref:!!!! 1605fk0bs

Solution

We can use -F, -a to cause @F = split /,/ in order to work with the third field and create a fourth.

perl -F, -pale'($F[3]) = $F[2] =~ /(?<!\d)([0-1]\d{3,4})(?!\w)/; $_=join(",", @F)'

Answered By - ikegami

Answer Checked By - Gilberto Lyons (WPSolving Admin)