Extended question to the issue: Regular expression substring for labelling

The answers I've found on the link above were fine for "simple" road labels.

Unfortunately, I don't have a big SQL-knowledge, therefore, I would like to extend Erik's question. How can we extract other road labels (alphanumeric) from the OSM lines layer "other_tags" between the ("ref"=>") and the last (")?


  1. "ref"=>"AP 520" -> AP 520
  2. "ref"=>"M123-4" -> M123-4
  3. "ref"=>"EN 13-5" -> EN 13-5
  4. "ref"=>"AG-52" -> AG-52

In order to avoid to write thousands of different expressions depending on each example, isn't any expression that extracts the content only of "ref"=>"xxxxxxxx"?

I tried everything using the "regexp_substr".

  • Can you post how the tag looks in the database. I would like to see how the quotation marks work in it. If you had a string String = ''' "ref"=>"AP 520" ''' and you wanted to only get 'AP 520' you would do , NewString = String.split(' " ')[-2] , that would give you 'AP 520' for the NewString.
    – ed.hank
    Dec 27 '17 at 20:50

Try the following:

regexp_substr("other_tags",'"ref"=>"([\\w- ]+)')

Should work assuming you have no other kinds of whitespaces, or unusual symbols/letters in the ref field.

It looks for "ref"=> then matches any word characters (A-Z, a-z, 0-9, underscore) as well as dash and space ([A-Za-z0-9-_ ]) and continues doing so (+) until it stops matching (in this case when it hits the double quotation mark).


This one worked for me:

regexp_substr( "other_tags", '\\"ref"=>"(.*?)\\"')

You can open your OSM file with the QuickOSM plugin. It will split your "other_tags" into fields automatically. If the process is too slow, you can provide a custom OSM Conf file to get the field you want in QuickOSM, "Open OSM file" tab.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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