559 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How to do calculations and expressions in T-SQL Part five of a five-part series of blogs |
---|
You can use SQL to do everything from simple arithmetic through to complicated functions - this blog gives you the low-down!
This blog is part of our full SQL online tutorial. You can learn how to write SQL on a Wise Owl SQL classroom course if you're in the UK. |
The final part of this blog looks at the thorny issue of nulls. You can get one of these in a table by pressing CTRL + 0, although usually they're already there!
In this table of people, we've removed one person's first name.
There are 3 possible ways to deal with nulls in expressions: using IsNull, Coalesce or CASE. I've explained these under separate headings below!
This function substitutes a given value when a column is null. The syntax is:
Here's an example, showing someone's first name for the table above:
-- show people's names
SELECT
IsNull(FirstName, 'Not given') AS 'First name',
LastName
FROM
tblPerson
Here's what this would give for our table above:
SQL has substituted the words Not given when the first name is null.
This strangely-named function allows you to try multiple values. The syntax is:
Here's an example, returning someone's phone number by trying various columns in turn:
-- get valid phone number
COALESCE(
MobileNumber,
WorkPhone,
HomePhone,
'No phone number given'
) AS Phone
You can always use COALESCE instead of ISNULL, by just including two arguments for it.
This is my personal favourite, since it builds on something with which every SQL programmer should be familiar - the CASE statement (see previous part of this blog). We could show the first name for the example at the start of this page as follows:
SELECT
FirstName,
-- show first name without null
CASE
WHEN FirstName is null THEN 'Not given'
ELSE FirstName
END AS 'First name',
LastName
FROM
tblPerson
This would give the same results:
The second column gives the first names of people, but with null values removed.
And that is the end of my blog on calculations in SQL!
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.