CODEWITHSUNDEEP
oracle-database
michaellorenz
michaellorenz

Reputation: 31

select NULL or BLANK or misc fields

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)

Upvotes: 0

Views: 29

Answers (2)

Bohemian
Bohemian

Reputation: 425003

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".

Upvotes: 1

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

  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
  1. 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

Upvotes: 1

Related Questions