I have a table where in the column have null values, blank spaces and misc values like !!, %% etc

I would like to select all the rows that do not have the Country ID.

How to select rows that do not have a Country ID?

Here is the SQL FIDDLE

http://sqlfiddle.com/#!4/bf3688/3

This is what I tried

SELECT * FROM PLAYER_TABLE WHERE COUNTRY_ID IS NULL OR LENGTH(TRIM (COUNTRY_ID)) = 0

Expected Output

PLAYER_ID   COUNTRY_ID

65  
98  (null)
99  (null)
13  !!
15  (null)
16  $$
18  
19  (null)
75  (null)

CodePudding user response：

1. Readable version: http://sqlfiddle.com/#!4/bf3688/5

SELECT * 
FROM PLAYER_TABLE 
WHERE COUNTRY_ID IS NULL 
OR regexp_replace(COUNTRY_ID,'[^A-Z]') is null

2. Fast version: http://sqlfiddle.com/#!4/bf3688/6

SELECT * 
FROM PLAYER_TABLE 
WHERE COUNTRY_ID IS NULL 
OR translate(COUNTRY_ID,'0ABCDEFGHIJKLMNOPQRSTUVWXYZ','0') is not null

CodePudding user response：

Use NOT REGEXP_LIKE(COUNTRY_ID, '[A-Z][A-Z]'):

SELECT *
FROM PLAYER_TABLE
WHERE COUNTRY_ID IS NULL
OR NOT REGEXP_LIKE(COUNTRY_ID, '[A-Z][A-Z]')

See live demo.

The regex means "two capital letters" so it's easy to understand the SQL: "where country_id is not two capital letters".