Today I was working on some queries where I needed to do the following:
- Replace a NULL value with 0 or another value
- Insert some text into a column which contains an apostrophe (I didn’t know how to escape the single quote)
1. COALESCE / ISNULL
I used COALESCE in the case where I had to have a value for something. This function will take the first non-null value from the list of arguments.
SELECT Manufacturer, Model, Colour, Price, COALESCE(Model, Colour, Manufacturer) AS FirstNonNull FROM TABLE1
The use of COALESCE above will take the first non null value listed. So if Model was NULL, it would take the value of Colour. The same happens if Colour is NULL, Manufacturer is taken as the value of “FirstNonNull”. This example isn’t a great real life example but I hope it explains it in simple terms.
If you’re dealing with numbers instead of text, you can also just use COALESCE like this:
COALESCE(Price, 0) AS FirstNonNull
Another way we can replace the NULL value is using ISNULL function built into SQL. If we just want a default when the value is NULL this is ideal. We can use it like this:
SELECT Manufacturer, Model, Colour, ISNULL(Price, 0) FROM TABLE1
This will just replace a NULL Price with 0 in this case. Again, not a fantastic example but hopefully shows the idea.
2. Escaping single quotes
I was inserting some text into a table today where I needed to use an apostrophe. Sometimes you will insert text which needs to be grammatically correct so this is an important thing to know.
What I needed to do was escape the single quote in the statement like so:
UPDATE TABLE1 SET Description = 'Have you checked your car's MOT?' WHERE ID = 1;
The compiler would pick up on the single quote or apostrophe in “car’s” and complain as the string would be finished after “car”. So what we need to do is add another carefully placed single quote to tell SQL Server to ignore this apostrophe and insert it into the table.
UPDATE TABLE1 SET Description = 'Have you checked your car''s MOT?' WHERE ID = 1;
Escaping quotes and other things like this can be useful when you may have to insert formatted text such as HTML into your table.
I’m no expert in this field, but since I have learned these things I want to try and pass them on to others. If you have any questions I’ll do my best to answer them!