CODEWITHSUNDEEP
sqlsqlite
user1414682
user1414682

Reputation: 147

How to select all values and hide NULL values in SQL?

so in my database some rows have NULL values, and when I select * from table, that NULL values also shows as text "null". So I want to hide all NULL values. Does anyone have idea for query? Thanks!

this is my input in DB:

db.execSQL("CREATE TABLE IF NOT EXISTS table (name VARCHAR, kg VARCHAR,   pod                 VARCHAR,reps VARCHAR, time VARCHAR );");
  db.execSQL("INSERT INTO table VALUES('name 1',NULL,NULL , NULL , '"+s+"');");
 db.execSQL("INSERT INTO table VALUES(NULL,'S 1','"+ee5+"' , '"+ee+"' , '"+s+"');");
 db.execSQL("INSERT INTO table VALUES(NULL,'S 2','"+ee6+"' , '"+ee2+"', '"+s+"');");
 db.execSQL("INSERT INTO table VALUES(NULL,'S 3','"+ee7+"' , '"+ee3+"', '"+s+"');");
 db.execSQL("INSERT INTO table VALUES(NULL,'S 4','"+ee8+"' , '"+ee4+"', '"+s+"');");

Upvotes: 1

Views: 44960

Answers (4)

wildplasser
wildplasser

Reputation: 44250

It depends on the data type of the column. 

-- If the data type is integer:
SELECT COALESCE(the_column, 0)
FROM the_table;

-- or, if the column is a char or varchar type:
SELECT  COALESCE(the_column, 'some text here')
FROM the_table;

-- or, if it is a date:
SELECT  COALESCE(the_column, '1900-01-01')
FROM the_table;

BTW: some databases have the IFNULL() function which does the same thing.

Upvotes: 4

nawfal
nawfal

Reputation: 73183

This is what you are looking for:

SELECT x, y, etc, CASE WHEN field IS NOT NULL THEN field ELSE '' END AS hehe FROM table;

Edit: Addin to your comments, this is pretty trivial once you know how to do it for one column. Apply the same for all columns. In SO dont expect homeworks to get done, instead expect a help to solve your problem which ultimately you yourself have to do.

Btw, here is how..

SELECT COALESCE(name, ''), COALESCE(kg, ''), COALESCE(pod, ''), COALESCE(reps, ''), 
       COALESCE(time, '') 
FROM   table

You have three good methods in this thread (including mine), and I personally feel the other two are more intuitive. Use any by applying the same logic as I have shown.

Upvotes: 3

fudo
fudo

Reputation: 219

in SQLite you should be able to do something like this:

SELECT col1, col2, ..., ifnull(coln, '') FROM TABLE

Upvotes: 3

dotmido
dotmido

Reputation: 1384

SELECT* FROM TableName
WHERE ValueCol IS NOT NULL

http://www.w3schools.com/sql/sql_null_values.asp

if you're working with sqlite for android

look at this Article

Upvotes: -2

Related Questions