[Qgis-user] Concatenate address fields with NULLS

[Andreas Neumann]

Hi Matthias and Jürgen,

Thanks for your clarifications. Now it makes more sense and I agree that 
the Concat version is easier to use.

Andreas

On 21.09.2015 10:26, Matthias Kuhn wrote:
> 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
>
>
>
> _______________________________________________
> 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/aab9fe3d/attachment.html>