Check if all characters are 'X'-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Another option(without using a regular expression) might be using

WITH t(col) AS
(
 SELECT 'XXY' FROM dual UNION ALL
 SELECT 'YXX' FROM dual UNION ALL
 SELECT 'XXX' FROM dual UNION ALL
 SELECT  NULL FROM dual UNION ALL
 SELECT 'XX ' FROM dual 
)
SELECT *
  FROM t
 WHERE REPLACE(NVL(col,'Y'),'X') IS NULL;

COL
----
XXX

without forgetting the case col = NULL through use of a NVL()

CodePudding user response：

You can use the following syntax (assuming you are using MySQL database 5.6 or greater version):

SELECT * FROM table_name WHERE col_name REGEXP '^X $';

CodePudding user response：

If you don't want/have regexp then:

WITH
    tbl AS
        (   Select 'XXY' "COL" From dual Union All
            Select 'YXX' "COL" From dual Union All
            Select 'XXX' "COL" From dual Union All
            Select null "COL" From dual 
        )
Select  COL 
From    tbl
Where   Length(Nvl(COL, 'Z')) - Length( Replace(  Upper(Nvl(COL, 'Z')), 'X', '')) Is Null

COL
---
XXX

This covers both small 'x' and capital 'X' if needed and returns original COL value