I am creating a view in athena from athena table where I need to get a substring from a column value. I have a column where values are like below:

|12 MO ABC 14.99APR/1.20PMT|

|36 MO ABC 14.99%APR/1.20%PMT|

|36 MO ABC 14.99% APR/1.20%|

|14 MONTH ABC9.99%APR/8.79%|

|14 MONTH ABC DEF 9.10% APR|

From these values I need to get only highlighted APR value. How can we get it?

CodePudding user response：

You can use:

SELECT regexp_extract('|36 MO ABC **14.99% APR**/1.20%PMT|', '[\d.%]  APR')

It will return:

14.99% APR

If the space before APR is sometimes missing, you can use:

SELECT regexp_extract('|14 MONTH ABC9.99%APR/8.79%|', '[\d.%]  ?APR')