<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
To clarify with a more meaningful example, the following two
expressions are equal<br>
* CONCAT( name, ', ', country )<br>
* COALESCE( name, '' ) || ', ' || COALESCE( country, '' )<br>
<br>
Example:<br>
Given features with the attributes:<br>
<br>
* Feature 1:<br>
name : 'Tokyo'<br>
country: 'Japan'<br>
* Feature 2:<br>
name: 'Atlantis'<br>
country: NULL<br>
<br>
The result is for both methods:<br>
* Feature1: 'Tokyo, Japan'<br>
* Feature2: ', Atlantis'<br>
<br>
PS: thanks for the hint with the quotes, I was to quick on the send
button<br>
<br>
Best regards,<br>
Matthias<br>
<br>
<div class="moz-cite-prefix">On 09/21/2015 08:39 AM, Andreas Neumann
wrote:<br>
</div>
<blockquote cite="mid:55FFA617.7060706@carto.net" type="cite">
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
Hi,<br>
<br>
So how is concat(NULL,fieldname) any better than
COALESCE(fieldname,''). To me it is the same complexity and not
really an improvement. But maybe I don't get it. The only slight
advantage would be that "concat" may be a more familiar term than
coalesce. But if you come from a database background you already
know COALESCE. Any serious GIS professional needs to know
databases.<br>
<br>
Another small thing:<br>
Do not mix up double quotes (") and single quotes ('). Whenever
you use a string, do use single quotes, when you use an attribute
either use no quotes or double quotes for some providers if you
use capital letters in field names (which is discourage anyway).<br>
<br>
Andreas<br>
<br>
<div class="moz-cite-prefix">On 20.09.2015 12:59, Matthias Kuhn
wrote:<br>
</div>
<blockquote cite="mid:55FE9175.6040706@opengis.ch" type="cite">
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
Hi Phil,<br>
<br>
In addition to COALESCE I'd like to promote the use of the
function <b>CONCAT()</b> which treats NULL values as empty
strings (it was changed recently, it's probably since 2.10) what
makes it very handy.<br>
<br>
Example:<br>
<br>
NULL || "hello" -> NULL<br>
<br>
vs.<br>
<br>
CONCAT( NULL , "hello") -> "hello<br>
<br>
vs.<br>
<br>
COALESCE( NULL, "" ) || "hello" -> "hello"<br>
<br>
Cheers,<br>
Matthias<br>
<br>
<div class="moz-cite-prefix">On 09/20/2015 09:54 AM, Phil (The
Geek) Wyatt wrote:<br>
</div>
<blockquote
cite="mid:005301d0f379$8ca06a60$a5e13f20$@wyatt-family.com"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="Generator" content="Microsoft Word 14 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal">Hi Folks,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I am working with LIST Address Points
data from <a moz-do-not-send="true"
class="moz-txt-link-freetext"
href="http://listdata.thelist.tas.gov.au/opendata/">http://listdata.thelist.tas.gov.au/opendata/</a>
(Specifically Clarence Municipality) and I need to
concatenate into one field the full address of each
location. I am struggling to figure out how to do it when
there are fields for unit numbers, building names, numbers
to and from etc. Many of the fields are also NULL so
clearly I want those fields disregarded.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Can someone give me a quick heads up on
how to NOT add the data from a field that has NULL?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Chances are I will be doing this
regularly so I am keen to document the required
expressions for others to use as well.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span
style="mso-fareast-language:EN-AU"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="mso-fareast-language:EN-AU">Cheers - Phil<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="mso-fareast-language:EN-AU"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="mso-fareast-language:EN-AU">Volunteer Mapper - <a
moz-do-not-send="true"
href="http://www.redcross.org.au/volunteering.aspx"><span
style="color:windowtext;text-decoration:none">Red
Cross</span></a><o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Qgis-user mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
<a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a></pre>
</blockquote>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Qgis-user mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
<a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a></pre>
</blockquote>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Qgis-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a></pre>
</blockquote>
<br>
</body>
</html>