Using substring for labeling in QGIS?

Question

I read Regular expression substring for labelling

and it got me curious. I'm trying to do something similar with OSM content in QGIS.

I would like to label features with English names if those names are stashed in the other_tags attribute. If English names do not exist in the other_tags attribute field, fall back to the name attribute.

I've gotten only as far as finding whether an English value exists:

"other_tags" LIKE '%"name:en"=>"%'

Perhaps someone could explain to me how to put all this in a conditional check within QGIS, calling the name:en value to be rendered as the label, and if the conditional check fails, use the name field.

I am on QGIS 2.14.

UPDATE:

I read through QGIS Multiple CASE WHEN/THEN Statements for Expression Based Labels

and it does not answer my question. I am not concerned with how to take multiple attributes and stack them in a label. I am looking at a single attribute field in OSM called other_tags. The structure of this tag looks like this:

"name:ar"=>"[some arabic text here]","name:ru"=>"[some russian text here]","name:en"=>"Gaziantep"....etc...

What I'm trying to do is take, for example, Gaziantep out of "name:en" out of the overall other_tags field.

I can use CASE expression to roll over to the regular name field if the conditional check fails, but how do I syntactically obtain the "name:en" subvalue?

• SaultDon: You can use a CASE statement - see if this answer addresses your problem gis.stackexchange.com/q/162297/1297 then it can be closed as a possible duplicate
• auslander: I think I may have spoken too soon. This link does not answer my question. This shows how to do conditional labeling based on an entire attribute - I'm trying to seek through a single attribute field in OSM (the dreaded "other_tags" attribute) and find a substring, and then find n number of characters in that string until another delimiter.
• geozelot: am I getting this wrong...can´t you just select any tag with flags (name::en) when exporting to spatiallite?
• auslander: @ThingumaBob I would like to keep all features in my view, naming those with name:en tags with that, and then if that doesn't exist, name it with whatever is in the name field. And even if I did that, I'm still left with the same labeling question, because it's not putting that name:en value into its own attribute field.

Answer 1

This might work - it was taken and adapted from https://gis.stackexchange.com/a/63339/1297

regexp_substr(right("other_tags", length("other_tags")-(strpos("other_tags",'"name:en"=>')+10)),'([^"]+)')

It will split the "other_tags" field at "name:en"=> and then remove the first 11 characters, essentially stripping out the "name:en"=> from the string. That's what the +10 is for.

So it would go from
"name:en"=>"some english name","name:fr"=>"some french name",... to
"some english name","name:fr"=>"some french name",...

So next is to then use the regular expression ([^"]+) to extract only the string it finds in the first set of double quotes, but without the double quotes.

The regular expression will take "some english name","name:fr"=>"some french name",... and return some english name.

So to put that as a CASE statement, use:

CASE WHEN "other_tags" LIKE '%"name:en"=>"%'
THEN regexp_substr(right("other_tags", length("other_tags")-(strpos("other_tags",'"name:en"=>')+10)),'([^"]+)')
ELSE "name"
END

I'm sure there is a way to do this with just the regexp_substr() function without having to split the field with the nested right(),length(),strpos() functions but I'm quite elementary with the regular expressions.

A more simplified answer would be welcome - would help me learn more too!

• auslander: Quite elementary? I'd say your regex-fu is pretty darn good. This gave me exactly what I needed.

Answer 2

I still might not get if these are the tags you are looking for, but when I download OSM data (for the region of Gizantep since you named it, via QGIS native OpenStreetMap-Dataloader [Vector -> Opnstreetmap -> Download data... -> Import topology from XML -> Export to SpatialLite]) and select the tag name and all tags with name:xx flag in the tag selector window, the exported and loaded layer attribute table from spatiallite include columns for each of them. When I use CASE WHEN "name:en" IS NOT NULL THEN "name:en" ELSE "name" ENDin the label settings, I think I get what you want?

(-> all Labels (name:en) in english if present, otherwise standard names (name), I somehow can ́t seem to include my screenshots here...)

EDIT: Now, after @SaultDon's answer it finally hits me: it's all in one column. I leave this here in case you will end up importing data via the plugin ,)