Solved

How to handle IS NULL

Posted on 2011-02-11
5
236 Views
Last Modified: 2012-05-11
I need to join three site address fields together into one string. The code works fine where there a re values in each of the three address fields. However as soon as one of the three fields is blank then the record returned is blank.

I have created this code to try and say if a field is blank put a "" . However it just keeps telling me there is a syntax problem.

CASE WHEN site_address2 IS NULL THEN (' ') ELSE site_address2
0
Comment
Question by:CMChalcraft
5 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34872517
Did you add END ?
CASE WHEN site_address2 IS NULL THEN ' ' ELSE site_address2 END
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34872580
use

COALESCE(Site_address2, ' ')
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 34872583
You could also just use the ISNULL(<field>, <value>) function.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34872778

use
coalesce(address1+',','')+coalesce(address2+',','')+coalesce(address3,'') as address


coalesce is a function which takes a list of columns/expressions and returns the first non null one from the left....


it is "equivalent" to your case statement...

case when address1+',' is null then '' else address1 end
+case when address2+',' is null then '' else address2 end
+case when address3 is null then '' else address3 end  

0
 

Author Closing Comment

by:CMChalcraft
ID: 34886204
Thanks to everyone who answered. Lowfatspread was the simplest solution. I have never heard of coalesce before.

Regards

Chris C
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms sql + top 1 for each customer 3 62
SQL query with cast 38 59
Please help for the below sql query. 1 29
Using this function 4 38
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question