January 3, 2017 at 2:44 pm
How can I write code to replace null with another column
Example: firstname is null .....replace with firstname1 if firstname1 is also null then replace firstname with firstname2
Firstname1 comes from CTE1 ,
firstname2 comes form Cte2
Firstname comes from a table.
January 3, 2017 at 11:11 pm
Take a look at the COALESCE function
January 4, 2017 at 7:43 am
Or use a CASE statement.
SELECT Col1, Col2,
CASE WHEN cte1.Col IS NULL THEN cte2.Col ELSE cte1.Col END AS MyCol
FROM ....
This assumes that you can join the CTEs to each other or to another table in the query, though.
January 4, 2017 at 8:30 am
I have Firstname , firstname 1 , firstname 2 ,firstname3
If firstname is null thne firstname1 , if firstname1 is also null then Firstname2 ...will the case will work ?
Case when Firstname is null then Firstname1
When Firstname1 is null then firstname2
When Firstname3 is null then Firstname
Else Firstname
End
Will this work with case ? as I have to get values for firstname if NUll then check these three firstname1, firstname2 , firstname3 ......? to get value either from three of the columns
January 4, 2017 at 8:43 am
komal145 (1/4/2017)
I have Firstname , firstname 1 , firstname 2 ,firstname3If firstname is null thne firstname1 , if firstname1 is also null then Firstname2 ...will the case will work ?
Case when Firstname is null then Firstname1
When Firstname1 is null then firstname2
When Firstname3 is null then Firstname
Else Firstname
End
Will this work with case ? as I have to get values for firstname if NUll then check these three firstname1, firstname2 , firstname3 ......? to get value either from three of the columns
This all depends on what you're doing. If this is in your result set, you want COALESCE. If in your JOIN statemnt, you want to use a CASE Statement.
For example:
--COALESCE Excample in your Resultset
SELECT COALESCE(Home_Num, Mobile_Num, Work_Num, Other_Num) AS Contact_Num
FROM Customer;
/*
CASE Statement example in JOIN,
although an OR statement could work just as well, if not better
it depends if you want any matching rows to return, or only
the first match.
*/
SELECT *
FROM Customer C
JOIN Phone P ON CASE WHEN C.Home_Num IS NOT NULL THEN C.Home_num
WHEN C.Mobile_Num IS NOT NULL THEN C.Mobile_Num
WHEN C.Work_Num IS NOT NULL THEN C.Work_Num
WHEN C.Other_Num IS NOT NULL THEN C.Other_Num END = P.Number;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 4, 2017 at 8:51 am
The main differences between CASE and COALESCE() are that COALESCE() is faster to type and CASE gives you other data manipulation options. Otherwise, they are mostly identical in the way they function (returning the first non-null value in the order in which you do your comparison).
I like CASE personally because it allows me to alter values being returned (or add new information) where as COALESCE just returns the original value.
January 4, 2017 at 10:31 am
komal145 (1/4/2017)
I have Firstname , firstname 1 , firstname 2 ,firstname3If firstname is null thne firstname1 , if firstname1 is also null then Firstname2 ...will the case will work ?
Case when Firstname is null then Firstname1
When Firstname1 is null then firstname2
When Firstname3 is null then Firstname
Else Firstname
End
Will this work with case ? as I have to get values for firstname if NUll then check these three firstname1, firstname2 , firstname3 ......? to get value either from three of the columns
If you think of a CASE expression as a decision tree, the TRUE expressions should lead to a final node and the FALSE expressions should lead to another branch. You've set yours up backwards. Instead of testing for Firstname IS NULL, you should be testing for Firstname IS NOT NULL.
SELECT
CASE
WHEN Firstname IS NOT NULL THEN Firstname
WHEN Firstname1 IS NOT NULL THEN Firstname1
WHEN Firstname2 IS NOT NULL THEN Firstname2
ELSE Firstname3
-- we could test for Firstname3 is not null as well, but if we're returning NULL when Firstname3 is NULL, just return Firstname3
END
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2017 at 10:45 am
Brandie Tarvin (1/4/2017)
I like CASE personally because it allows me to alter values being returned (or add new information) where as COALESCE just returns the original value.
But you can manipulate the values in a COALESCE. For example, it comes in handy when working with names, especially when you are often missing any of Title, MI, and/or Suffix. It can be very complicated to come up with a formula that works with all possible combinations, especially getting the spaces and punctuation correct, but it's relatively easy with COALESCE.
SELECT COALESCE(
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name,
Title + ' ' + First_Name + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + Last_Name,
First_Name + ' ' + Last_Name,
Title + ' ' + Last_Name
)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2017 at 10:50 am
drew.allen (1/4/2017)
Brandie Tarvin (1/4/2017)
I like CASE personally because it allows me to alter values being returned (or add new information) where as COALESCE just returns the original value.But you can manipulate the values in a COALESCE. For example, it comes in handy when working with names, especially when you are often missing any of Title, MI, and/or Suffix. It can be very complicated to come up with a formula that works with all possible combinations, especially getting the spaces and punctuation correct, but it's relatively easy with COALESCE.
SELECT COALESCE(
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name,
Title + ' ' + First_Name + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + Last_Name,
First_Name + ' ' + Last_Name,
Title + ' ' + Last_Name
)
Drew
Yeah, but can it do "If last name = Tarvin then $100.00" ? Because I do a lot of that when creating files for different business needs.
EDIT: We have a lot of situations where we have to write CASE statements to evaluate multiple columns at once to come up with an answer that has nothing directly to do with the columns being evaluated. "If it is Tuesday and Drew is wearing a purple tie and it rained around 2:00 p.m., then choose 'availabile'" kind of switch statements. Can COALESCE do that?
January 4, 2017 at 11:28 am
Brandie Tarvin (1/4/2017)
drew.allen (1/4/2017)
Brandie Tarvin (1/4/2017)
I like CASE personally because it allows me to alter values being returned (or add new information) where as COALESCE just returns the original value.But you can manipulate the values in a COALESCE. For example, it comes in handy when working with names, especially when you are often missing any of Title, MI, and/or Suffix. It can be very complicated to come up with a formula that works with all possible combinations, especially getting the spaces and punctuation correct, but it's relatively easy with COALESCE.
SELECT COALESCE(
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name,
Title + ' ' + First_Name + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + Last_Name,
First_Name + ' ' + Last_Name,
Title + ' ' + Last_Name
)
Drew
Yeah, but can it do "If last name = Tarvin then $100.00" ? Because I do a lot of that when creating files for different business needs.
EDIT: We have a lot of situations where we have to write CASE statements to evaluate multiple columns at once to come up with an answer that has nothing directly to do with the columns being evaluated. "If it is Tuesday and Drew is wearing a purple tie and it rained around 2:00 p.m., then choose 'availabile'" kind of switch statements. Can COALESCE do that?
CASE is clearly more flexible than COALESCE, because it can perform any conditional test whereas COALESCE only tests whether an expression is NULL, and because it can return any scalar expression whereas COALESCE returns the first non-NULL expression evaluated if one exists. However, I did want to emphasize that it was possible to manipulate the expressions that COALESCE is evaluating.
Drew
PS: I believe that COALESCE is implemented as a CASE expression internally.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2017 at 1:24 pm
drew.allen (1/4/2017)
Brandie Tarvin (1/4/2017)
I like CASE personally because it allows me to alter values being returned (or add new information) where as COALESCE just returns the original value.But you can manipulate the values in a COALESCE. For example, it comes in handy when working with names, especially when you are often missing any of Title, MI, and/or Suffix. It can be very complicated to come up with a formula that works with all possible combinations, especially getting the spaces and punctuation correct, but it's relatively easy with COALESCE.
SELECT COALESCE(
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name,
Title + ' ' + First_Name + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + Last_Name,
First_Name + ' ' + Last_Name,
Title + ' ' + Last_Name
)
Drew
I prefer to evaluate each column on its own.
SELECT *, COALESCE(
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name,
Title + ' ' + First_Name + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + Last_Name,
First_Name + ' ' + Last_Name,
Title + ' ' + Last_Name
),
LTRIM( COALESCE(Title, '')
+ COALESCE( ' ' + First_Name, '')
+ COALESCE( ' ' + MI, '')
+ COALESCE( ' ' + Last_Name, '')
+ COALESCE( ', ' + Suffix, ''))
FROM(
VALUES ('Mr.', 'Luis', 'A', 'Cazares', 'Sr.'),
(NULL, 'Luis', 'A', 'Cazares', 'Sr.'),
(NULL, 'Luis', NULL, 'Cazares', 'Sr.'),
(NULL, 'Luis', NULL, 'Cazares', NULL),
('Mr.', 'Luis', NULL, 'Cazares','Sr.'),
('Mr.', 'Luis', 'A', 'Cazares', NULL),
('Mr.', NULL, NULL, 'Cazares', NULL),
('Mr.', 'Luis', NULL, NULL, NULL),
('Mr.', NULL, NULL, 'Cazares', 'Sr.'),
(NULL, NULL, 'A', 'Cazares', NULL))x(Title, First_Name, MI, Last_Name, Suffix)
January 4, 2017 at 2:33 pm
Luis Cazares (1/4/2017)
drew.allen (1/4/2017)
Brandie Tarvin (1/4/2017)
I like CASE personally because it allows me to alter values being returned (or add new information) where as COALESCE just returns the original value.But you can manipulate the values in a COALESCE. For example, it comes in handy when working with names, especially when you are often missing any of Title, MI, and/or Suffix. It can be very complicated to come up with a formula that works with all possible combinations, especially getting the spaces and punctuation correct, but it's relatively easy with COALESCE.
SELECT COALESCE(
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name,
Title + ' ' + First_Name + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + Last_Name,
First_Name + ' ' + Last_Name,
Title + ' ' + Last_Name
)
Drew
I prefer to evaluate each column on its own.
SELECT *, COALESCE(
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name,
Title + ' ' + First_Name + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + Last_Name,
First_Name + ' ' + Last_Name,
Title + ' ' + Last_Name
),
LTRIM( COALESCE(Title, '')
+ COALESCE( ' ' + First_Name, '')
+ COALESCE( ' ' + MI, '')
+ COALESCE( ' ' + Last_Name, '')
+ COALESCE( ', ' + Suffix, ''))
FROM(
VALUES ('Mr.', 'Luis', 'A', 'Cazares', 'Sr.'),
(NULL, 'Luis', 'A', 'Cazares', 'Sr.'),
(NULL, 'Luis', NULL, 'Cazares', 'Sr.'),
(NULL, 'Luis', NULL, 'Cazares', NULL),
('Mr.', 'Luis', NULL, 'Cazares','Sr.'),
('Mr.', 'Luis', 'A', 'Cazares', NULL),
('Mr.', NULL, NULL, 'Cazares', NULL),
('Mr.', 'Luis', NULL, NULL, NULL),
('Mr.', NULL, NULL, 'Cazares', 'Sr.'),
(NULL, NULL, 'A', 'Cazares', NULL))x(Title, First_Name, MI, Last_Name, Suffix)
This was actually a simplified version. We had more complex rules. For example, a full name should not include Dr. in the title and MD in the suffix. You can use Dr. in the title or MD in the suffix, but you should never use both. DMDs (or maybe it was PhDs) on the other hand want you to use both the Dr. in the title and the DMD in the suffix. :crazy:
Plus, we had options to allow a person to specify how they wanted their full name to appear.
And then we had options for how the name should appear depending on where it was being used. For doctors, we used the form with MD in the suffix for addresses, but used the form with Dr. in the title for salutations.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 5, 2017 at 12:44 am
Both options have their uses for sure.
I didn't see anybody making this point, but COALESCE() absolutely needs a non-null argument or it will throw an error where a case without a "hit" will use the ELSE clause (or null where applicable).
COALESCE(NULL,NULL)
will get you
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
January 5, 2017 at 4:36 am
drew.allen (1/4/2017)
CASE is clearly more flexible than COALESCE, because it can perform any conditional test whereas COALESCE only tests whether an expression is NULL, and because it can return any scalar expression whereas COALESCE returns the first non-NULL expression evaluated if one exists. However, I did want to emphasize that it was possible to manipulate the expressions that COALESCE is evaluating.
Hence the reason I prefer it.
PS: I believe that COALESCE is implemented as a CASE expression internally.
You are correct on that. I just wish I could do my stupid scenario-based switching with it. I like options that let me type less. :hehe:
January 5, 2017 at 4:37 am
Rasmus Remmer Bielidt (1/5/2017)
Both options have their uses for sure.I didn't see anybody making this point, but COALESCE() absolutely needs a non-null argument or it will throw an error where a case without a "hit" will use the ELSE clause (or null where applicable).
COALESCE(NULL,NULL)will get you
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
At least CASE let's you specify an ELSE for "none of the above".
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply