Return a blank string for a field SQL

Question

Is there a way to return a blank string for a field in SQL, even if the field has data?

SELECT NAME, AGE, DOB, GENDER, CITY
FROM TABLE

I want DOB, to always return blank ''

we can put the literal string in the SELECT list and give it a column alias, e.g. `SELECT NAME, AGE, ' ' AS DOB, GENDER, CITY FROM TABLE`. (If we don't need to return the value of the DOB column, then we don't reference the column in the SELECT list.) To return a blank, we need to return a space character `' '`; Oracle interprets a zero length string literal `''` as a NULL value. — spencer7593, Sep 15 '17 at 14:43

score 2 · Accepted Answer · answered Sep 15 '17 at 14:49

2

So you can just do:

SELECT NAME,
       AGE,
       NULL AS DOB,
       GENDER,
       CITY
FROM   TABLE

If you want to give it a specific data type then you can use CAST:

SELECT NAME,
       AGE,
       CAST( NULL AS DATE ) AS DOB,
       GENDER,
       CITY
FROM   TABLE

answered Sep 15 '17 at 14:49

MT0

I am trying to add a column name but I am getting an error for the null field. Is there a way to do this? – Agent_Sully Sep 15 '17 at 16:25
@Agent_Sully, try reading the error message. Note: we can't see your computer screen. – Jeffrey Kemp Sep 19 '17 at 03:42

BWS · Answer 2 · 2017-09-15T14:52:39.410

1

try this:

SELECT NAME, AGE, ' ' as DOB, GENDER, CITY
FROM TABLE

edited Sep 15 '17 at 14:52

answered Sep 15 '17 at 14:41

BWS

A string containing the space character is not the same as an empty string. – MT0 Sep 15 '17 at 14:57

score 0 · Answer 3 · answered Sep 15 '17 at 14:41

0

You could use NVL2:

SELECT NVL2(dob, '', NULL) --rest of cols
FROM TABLE;

Please note that '' is the same as NULL. So you have:

SELECT '' AS DOB -- rest of cols
FROM TABLE;

answered Sep 15 '17 at 14:41

Lukasz Szozda

`NVL2(dob, '', NULL)` is the same as `NVL2(dob, NULL, NULL)` which will always give the output `NULL` - so using `NVL2` is just over-complication. – MT0 Sep 15 '17 at 14:55
@MT0 Yup, that is why there is simple second part. I just wanted to show how to easily compare with `NULL` without using `CASE`. – Lukasz Szozda Sep 15 '17 at 14:55

3 Answers3