3

first: my screenshot

enter image description here

What I want: the two fields here only with "Straße:" should be empty. The other four results are ok. What I have done is:

CASE WHEN  "Straße/Nr."  IS NOT NULL THEN   ('Straße: '  +  "Straße/Nr.") END

Any ideas?

Improve this question

1 Answer 1

Reset to default
3

It looks like the Straße/Nr value for the last two lines in the table is an empty string (''), rather than NULL. If they were NULL, it would usually say NULL in pale or gray lettering.

You can account for this by replacing the "Straße/Nr." is not null part of your expression with length("Straße/Nr.")>0. Or simply add and "Straße/Nr." is not ''. Either of the following should work:

CASE WHEN length("Straße/Nr.")>0 THEN ('Straße: ' + "Straße/Nr.") END

or 

CASE WHEN "Straße/Nr." IS NOT NULL AND "Straße/Nr." is not '' THEN ('Straße: ' + "Straße/Nr.") END

On the other hand, it's possible that some of the Straße/Nr. values are actually strings with whitespace in them (eg, ' '). In that case, use the trim function to remove starting and ending whitespace, like this:

CASE WHEN length(trim("Straße/Nr."))>0 THEN ('Straße: ' + "Straße/Nr.") END
Improve this answer
6
  • Hello csk, CASE WHEN length(trim("Straße/Nr."))>0 THEN ('Straße: ' + "Straße/Nr.") END This is the solution! (The two other ideas do not change my result) Thank you very much for solution and very quickly answer!
    – juko
    Commented Nov 14, 2019 at 18:20
  • In that case it might be a good idea to clean up your data by using the Field Calculator to update the Straße/Nr. field with the expression trim("Straße/Nr."). That will permanently remove any values that are just spaces, converting them into empty strings. It will also remove any extra spaces at the start or end of Straße/Nr. values, eg if you had 'Ackerstraße 86 ' it would convert it to 'Ackerstraße 86'.
    – csk
    Commented Nov 14, 2019 at 18:35
  • Yes. Perfect! I used the trim command. After that I can successfully use your solutions above. Question: Could you tell me tutorials/websites for this commands, it should be easy for beginners and I need examples..... Thank you
    – juko
    Commented Nov 15, 2019 at 8:02
  • Hmm. I have a follow question to my case: Unfortunately I have no success with other fields, before edited with the trim order. The difference is: The field "Straße/Nr." I have edited before with a string function command to union Straße and Nr ("Straße"+ ' ' +"Hausnummer"). But the other columns I used only the trim order. But in the result I see again "Straße:"- but the result should be also empty fields, if these columns have no value. Any idea?
    – juko
    Commented Nov 15, 2019 at 9:45
  • edit: all Ok. I have had a mistake in my code.....
    – juko
    Commented Nov 15, 2019 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.