Search code examples
sqlhiveprestoamazon-athenatrino

Athena Query to match Alpha numeric Character at a fixed length in a colum data

In AWS Athena, how should i write query to get column values when the values is A-Z after dot'.'.

List of Column Values are :

DT90411.A7
CT90411.23 
CT90411.Q3

Currently i am not able to fetch value that have A-Z characters after dot'.'

Expected result :

DT90411.A7 
CT90411.Q3

These have characters A-Z after dot'.'

Select Col_A from Table1 where Col_A  like '%.[a-z]%'; is not giving the desired result
Solution

  • Use regexp_like.

    Demo:

    with Table1  as(
SELECT * FROM (
    VALUES
        'DT90411.A7', 'CT90411.23' ,'CT90411.Q3' 
) AS t (Col_A )
)
  
select Col_A
from Table1 
where regexp_like(Col_A,'(i?)\.[A-Z]')

    Result:

    Col_A

DT90411.A7
CT90411.Q3

    Regexp '(i?)\.[A-Z]' means:

    (i?) - case insensitive, remove it is you need case sensitive match

    \. - dot

    [A-Z] - character class, letters A to Z