SQL Server 101 – Podcasts 007 and 008
Find out more about the SQL Server 101 Podcast.
In Episode 7 of the SQL Server 101 podcast, we look at the following topics:
Strings
In a previous podcast, we tried to add a string to a numeric field, and found that it didn’t work. In this section, we’ll have a look at the two different main versions, CHAR and NCHAR, and finding out the difference between them (CHAR using ASCII and NCHAR uses Unicode – but I’ll explain what that is). There’s a bit advantage to using NCHAR compared to CHAR, but there’s a bit disadvantage as well – literally.
Then we’ll see why CHAR and NCHAR are not used too often, and what VARCHAR and NVARCHAR are used much more. We’ll see how to calculate the number of bytes that are used to store the data, and see when you should use CHAR compared to VARCHAR.
String Functions
Now that we have got our VARCHAR and NVARCHAR, we’ll have a look at the maximum size, and see some older versions (text, ntext and image).
How can you get part of a string in a SELECT statement. We’ll have a look at extracting a section, which in Excel would be the LEFT, MID and RIGHT string functions – we’ll see what the SQL Server equivalents are.
How can get you get of blank space, such as the space in ” hello” or “hello “? In Excel you can use the TRIM function, but that is only available in SQL Server 2017 and onwards. We’ll see what the more usual version in SQL Server is.
We’ll also see how to put strings in UPPER CASE and lower case (But There Is No Proper Case Function Built Into Sql Server), and a few other functions.
In Episode 8 of the SQL Server 101 podcast, we look at the following topics:
NULL – an introduction
Are you enjoying this Podcast? Well, you might say “yes” or you might say “no”. What if you are not sure? Well, this is a third option, which in SQL Server is called NULL. It is an non-definitive answer, neither “yes” or “no”.
So if NULL is an unknown answer, we’ll answer the question: what is NULL + 2? Is it 2? Also, what is the first two characters of a NULL string?
We’ll have a look at converting from a string field to a number field – but what happens if it fails? We’ll have a look at the TRY_CONVERT and TRY_CAST functions which convert the answer to NULL if it fails (as opposed to giving an error message).
Joining two strings together
If you have got two string fields, how do you join them together. In other languages, you would use an ampersand &. However, the standard way in SQL Server is using a plus + sign.
Unfortunately, there is a problem with this. What if one of the fields you are concatenating together is NULL? We’ll find out what happens, and several alternatives on how to avoid this.
One alternative is using the IIF function (yes, that’s IIF with two Is). Another is using the word CASE, which allows you to choose more than 2 outcomes. However, there may be an easier way to avoid NULLs – we’ll find out.
If you liked this podcast, why not subscribe on iTunes?
