Menu
I try to filter a database but unluckily I lose the 'null' values either way:
The Sample looks like
Name | City | Sold
Nike | NYC | 15
null | SFO | 20
Mega | SEA | 10
null | null | 8
nike | CHI | 12
I try to look for the data without Nike in any way written but when I go for
Select ...
where not lower(Name) contains "nike"
but then I find only
Mega | SEA | 10
and all the rows with null values for Name are gone as well. How can I prevent their deletion? Thanks
652
The syntax is as follows: Select * from table_source where column is not NULL; If you want to read more about the where operator, please refer to the documentation. In addition if you want to replace the null values, you can use the IFNNULL() function.
COALESCE(expr[, ... ]) Description. Returns the value of the first non-null expression. The remaining expressions are not evaluated.
For example, if you replace NULL with the empty string (or the string "null" , or something else that doesn't occur elsewhere in your data), the join will work as you expect. You can even do this on the fly by using a subquery, at minimal performance cost.
NVL Function in BigQuery The NVL function lets you replace null (returned as a blank) with a string in the results of a query. You can use following BigQuery built-in functions as an NVL alternative: BigQuery IFNULL as an NVL Alternative.
See this:
select * from
(select string(NULL) as name,'SFO' as city, 20 as sold),
(select 'Nike' as name,'NYC' as city, 15 as sold),
where not lower(name) contains 'nike2' or name is null
returns
+-----+------+------+------+---+
| Row | name | city | sold | |
+-----+------+------+------+---+
| 1 | null | SFO | 20 | |
| 2 | Nike | NYC | 15 | |
+-----+------+------+------+---+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
