How to not include null values in sql query
Practice Activity: Replacing NULL and blank values in Microsoft SQL Server
Timecodes:
[Music]
how can you replace blank values with
something more meaningful
hello i'm philip burton of idoodata.com
so here we have an example of a table
and we're just going to just look at all
of the code first of all we've got drop
table if exists because i don't want to
have to run this code lots of times and
then find it fails because the table
already exists
this drop table if exists only works
with sql server 2016 service pack one or
later so if you've got a more recent
version this will work
if not i suggest you just don't include
this line
then i create a table with a single
column and that column is called text
field
it's a varchar20 and it allows null
values
then we're inserting into this table the
words first value
second value
a blank which is not the same as a null
so a null is the absence of data a blank
is data it just so happens that it's
zero characters long
so if you take for instance the words i
do data what's the fourth word from it
well if you want to say that it is empty
there is no fourth word then you would
say no if however you want to say that
there is a word but zero characters and
then you would use two single
apostrophes now do be careful
that with two single apostrophes is not
the same as that which is a speech
mark so here we have got our table
but it's not very user-friendly if there
was nothing here then could it say
nothing or empty or blank or something
like that
so that's the subject of this video
so there are lots of ways to do this
depending partly on whether you want
nose as well as blank strings or empty
strings
so let's have a look at a few ways
first of all you've got the one word
is null
now you might be used to using is null
when it's not one word for example where
this text field
is no
so if how to run that then that will
just return one row which is the no row
alternatively you could say is not no
which would return the other three rows
including
the row which happens to have zero
characters
so that is is null which is two words it
is used in the where clause
however the one word is null is a
function
and what it says is if what i'm about to
give you which is the field happens to
be null
then
output something different so i'm going
to output
blank
in brackets
so this is my second
field
so this would be a second column over
here so let's see what the answer is
and you can see that it has replaced
null with blank now another way of doing
this is through the word
coalesce
and it has exactly the same sort of
syntax
so here we have my third field and
exactly the same result
the difference between the two well
coalesce i could put in additional
fields so if i had another field called
null
for instance which happens to contain
null then it would go to my third field
it would give me the first non-blank
answer i can't do that with is null it
only allows me to put in two arguments
no more
another difference is that coalesce can
be used in more than just the tsql
language is null is the one which i
frequently use it's just for tsql
purposes but it's much easier for me to
write is null as one word than to try
and remember how to spell coalesce
so this is fine apart from the fact that
if the string is empty in other words
it's not null it just happens to have
zero characters it will not be changed
and the reason for that
is because well it's not null
so for this we need to use a different
approach we can use case
so case is like an if function in other
languages so i say if something happens
to be true then give me this output if
something else happens to be true then
give me this output and so on
so i can say
have a look at text field
when it happens to
be zero characters then
give me the output of blank
else
so in all of the situations give me my
text field
so you can see there's a lot more words
here but i've got a lot more control
over what i'm having as the output
so you can see now we have our empty
string as being the string blank in the
output our null goes back to null
because it is not an empty string
okay let's expand this
let's put in
when
null
then
blank
and this will be
our
fifth
field
so when i use case like this
case and then i have a central thing
that i'm testing multiple times then
while you could
call it an if statement you might also
want to call it a switch or a choose in
other languages
so let's see if this works
and you can see no it doesn't so i can't
say in a case case this
when null it's just like if i was to
have
my select statement
and then have a where
clause and say where this equals null i
can't do this with null this will never
be true
because null does not equal null imagine
null being the absence of data data you
don't know is data that you don't know
equal to
some other data that you don't know the
answer is you don't know so it's never
true it's never false it's i don't know
it's always know as the answer so this
doesn't work
so if this doesn't work how can we
capture both
and the answer is to move
this text field
so i move it the other side of the when
so when this text field is equal to an
empty string then give me this text
when
this text field
is null as opposed to equals null then
give me this field otherwise give me
the field itself
so now let's have a look at it
and now you can see that it does work
where it is a
zero character empty string
then it's called by this condition and
so it gives me blank
where it is null it's called by this one
so i can
just
clarify this in the output by adding the
words empty and null so they're not
being called by the same condition they
are being called by different conditions
and if neither of these two conditions
happen to be true then it just goes
through to what i put after the else if
you don't put anything in the else well
it's like if i said else null
so generally you do want to use the else
in a case and then end
ends this case you do need it with a
case if you don't then you're going to
get a syntax error
and as always with formulas i would
strongly suggest that you give the
column an algus a name so in this video
we have gone through a table and we have
replaced
an empty string and a null
with blank
if you like this video and want more
from me then why not have a look through
the rest of this youtube channel or have
a look at my udemy courses if you like
something more comprehensive and in an
order
so it depends how much time you've got
if you've got an hour then join me for
my sql server essentials in an hour
where i'll go through this select
statement if you've got a bit more time
then have a look at my database
fundamentals
where i go through additional things
such as backups restore security insert
update delete and joining tables
together
and if you've got a lot more time and
you want to go really in-depth then
please join me for my microsoft sql
server with tsql course
it's 29 hours long but the good news is
that you can start at any particular
session and
find something that would be of interest
for you
if you like this video then please click
the like button and please press that
subscribe button and click that bell
that way you'll be notified of any new
videos thank you very much for watching
this and keep learning
[Music]
you
Related queries:
how to include null values in sql query
how to exclude null values in sql query
how to include null values in sql select
how to include null values in oracle sql query
how to select not null values in sql query
how to check not null value in sql server stored procedure
how to exclude null values in sql
non null values in sql
don't show null values in sql
