Using Athena SQL with regex

user_5416828 0
Viewed 10251

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.

amazon-athena sql
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.

user_18632290

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;
user_3768030
Related