Using Athena Sql with Regex
I'm Using Dbvisualizer to Connect to an Athena Instance. I Have a Working Query: Select Device, Description, Id, Size, Date from Test. Database Where Month =...
I'm using DbVisualizer to connect to an athena instance. I have a working query:
SELECT device, description, id, size, date FROM test.database WHERE month = '01'
and device not like '%link%'
and device not like '%Link%'
and device not like '%LINK%'
and id not like '%abc%'
and id not like '%Abc%'
and id not like '%ABC%'
group by device, description, id, size, date order by month desc
What I would like to do is clean it up and catch the cases using regex. I'm pretty sure /link.*/ig and /abc.*/ig would catch the case changes it but I don't know how to insert it in. I could not get "input.regex" = to work either.
2 Answers
You may use REGEXP_LIKE here:
SELECT DISTINCT device, description, id, size, date
FROM test.database
WHERE
month = '01' AND
NOT REGEXP_LIKE(device, '[lL]ink|LINK') AND
NOT REGEXP_LIKE(device, '[aA]bc|ABC')
ORDER BY
month DESC;
Note that your GROUP BY logic can also just be represented by a distinct select, for which I have opted above.
Assuming you want to just ignore case, you could probably simplify the suggestion from Tim Biegeleisen by writing:
SELECT DISTINCT device, description, id, size, date
FROM test.database
WHERE
month = '01' AND
NOT REGEXP_LIKE(device, '(?i)link|abc')
ORDER BY
month DESC;
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
