Contents
How to test for null in case statement?
In this form each boolean_expression is evaluated sequentially and the first to return true is executed. Here you can use the IS operator that is used to test for NULL values. e.g. WHEN column IS NULL THEN. There is a way to test for NULL in the scalar form of the CASE Statement above.
When to add a case to a SELECT statement?
As you write an SQL query, you may need to get values from multiple columns and change values from one form to another. The simple way to achieve this goal is to add a CASE expression to your SELECT statement.
When to use the name of the column in a case expression?
In a simple CASE expression, the name of the column or expression to be evaluated is absolutely necessary. It goes after the CASE keyword. The set value goes after the WHEN. If the evaluated value is the same the as the set value, the result defined in THEN is returned.
Is there a problem with the case statement?
The CASE statement has 2 forms which need to be understood. WHEN scalar_expression2 THEN WHEN scalar_expression3 THEN ELSE In this form, scalar_expression1 is comapred sequntially using = with each WHEN clause and the first match is executed. However NULL = NULL if false and hence you can’t use this form in your SQL
How to do a case insensitive text replace, ie?
I have searched extensively and failed to find a means to do a case insensitive replace, ie. replacing flag with frog will replace, FLAG, fLaG, etc. with frog and will change FlagOne to frogOne. Can someone help?
How to create a custom text replace function?
Text_ReplaceSubstring (extension of previous Text_ReplaceIgnoreCase function) It’s odd that some of these text functions don’t have a Comparer.OrdinalIgnoreCase option. My suggestion would be to create a custom function query named Text_ReplaceIgnoreCase, with the following code: Then apply the function in a step, e.g.: