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.


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?

  • 4
    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
    – SaultDon
    Commented May 23, 2017 at 21:10
  • 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.
    – auslander
    Commented May 24, 2017 at 15:02
  • am I getting this wrong...can´t you just select any tag with flags (name::en) when exporting to spatiallite?
    – geozelot
    Commented May 24, 2017 at 17:58
  • @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.
    – auslander
    Commented May 24, 2017 at 18:15

2 Answers 2


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"

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!

  • Quite elementary? I'd say your regex-fu is pretty darn good. This gave me exactly what I needed.
    – auslander
    Commented May 24, 2017 at 20:16

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 ,)

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.