<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Matthias and Jürgen,<br>
<br>
Thanks for your clarifications. Now it makes more sense and I agree
that the Concat version is easier to use.<br>
<br>
Andreas<br>
<br>
<div class="moz-cite-prefix">On 21.09.2015 10:26, Matthias Kuhn
wrote:<br>
</div>
<blockquote cite="mid:55FFBF1A.5070100@opengis.ch" type="cite">
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
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 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>