Criteria on char(1) field

Question

In oracle,

In a table, I have a field char(1). Field value are 0, 1 or empty field are empty

so i tried to do

select p.id, p.confidential_phone
from person p
where p.confidential_phone=''

No result are retourned

I tried with = ' ' is null

same result

If `is null` does not work it is possible, that you have some whitespace characters like [here](https://dbfiddle.uk/NVH7XCOl)?. — Ponder Stibbons, Apr 14 '23 at 18:00
Just to mention, setting a `char` column to `''` in SQL is different to assigning `a := ''` in PL/SQL. Yet another reason to [avoid `char`](https://stackoverflow.com/a/42165653/230471). Why not use the standard `varchar2`? — William Robertson, Apr 15 '23 at 09:49

markalex · Answer 1 · 2023-04-14T17:19:11.753

2

'' in oracle is NULL. Never do = '', use IS NULL instead.

Your query should work like this:

select p.id, p.confidential_phone
from person p
where p.confidential_phone is null

Consider this demo

edited Apr 14 '23 at 17:19

answered Apr 14 '23 at 17:10

markalex

score 1 · Answer 2 · answered Apr 14 '23 at 17:12

Oracle teats empty strings as NULL, so IS NULL will work

CREATE TABLE t1 (id int, confidential_phone char(1))

INSERT INTO t1 VALUES (1,'1')

INSERT INTO t1 VALUES (2,'0')

INSERT INTO t1 VALUES (3,'')

INSERT INTO t1 VALUES (4,NULL)

SELECT * FROM t1 WHERE confidential_phone IS NULL

ID	CONFIDENTIAL_PHONE
3	null
4	null

2 Answers2