Check if all characters are 'X'

Question

I have the below table:

COL
---
XXY
YXX
XXX
NULL

I want to filter out the rows which don't consist of all 'X's. Expected output:

COL
---
XXX

Answer 1

We can use REGEXP_LIKE here:

SELECT COL
FROM yourTable
WHERE REGEXP_LIKE(COL, '^X+$');  -- ^X+$ means all X from start to end

Another similar version:

SELECT COL
FROM yourTable
WHERE NOT REGEXP_LIKE(COL, '[^X]');  -- this means no non X present