[Qgis-user] Concatenate address fields with NULLS

[Matthias Kuhn]

To clarify with a more meaningful example, the following two expressions
are equal
 * CONCAT( name, ', ', country )
 * COALESCE( name, '' ) || ', ' || COALESCE( country, '' )

Example:
Given features with the attributes:

* Feature 1:
    name : 'Tokyo'
    country: 'Japan'
* Feature 2:
    name: 'Atlantis'
    country: NULL

The result is for both methods:
* Feature1: 'Tokyo, Japan'
* Feature2: ', Atlantis'

PS: thanks for the hint with the quotes, I was to quick on the send button

Best regards,
Matthias

On 09/21/2015 08:39 AM, Andreas Neumann wrote:
> Hi,
>
> 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.
>
> Another small thing:
> 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).
>
> Andreas
>
> On 20.09.2015 12:59, Matthias Kuhn wrote:
>> Hi Phil,
>>
>> In addition to COALESCE I'd like to promote the use of the function
>> *CONCAT()* which treats NULL values as empty strings (it was changed
>> recently, it's probably since 2.10) what makes it very handy.
>>
>> Example:
>>
>> NULL || "hello" -> NULL
>>
>> vs.
>>
>> CONCAT( NULL , "hello") -> "hello
>>
>> vs.
>>
>> COALESCE( NULL, "" ) || "hello" -> "hello"
>>
>> Cheers,
>> Matthias
>>
>> On 09/20/2015 09:54 AM, Phil (The Geek) Wyatt wrote:
>>>
>>> Hi Folks,
>>>
>>>  
>>>
>>> I am working with LIST Address Points data from
>>> http://listdata.thelist.tas.gov.au/opendata/ (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.
>>>
>>>  
>>>
>>> Can someone give me a quick heads up on how to NOT add the data from
>>> a field that has NULL?
>>>
>>>  
>>>
>>> Chances are I will be doing this regularly so I am keen to document
>>> the required expressions for others to use as well.
>>>
>>>  
>>>
>>>  
>>>
>>> Cheers - Phil
>>>
>>>  
>>>
>>> Volunteer Mapper - Red Cross
>>> <http://www.redcross.org.au/volunteering.aspx>
>>>
>>>  
>>>
>>>
>>>
>>> _______________________________________________
>>> Qgis-user mailing list
>>> Qgis-user at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>>
>>
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20150921/adce06cb/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: OpenPGP digital signature
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20150921/adce06cb/attachment.sig>