SQLite extract string from text in column

Question

I have a Spatialite Database and I've imported OSM Data into this database. With the following query I get all motorways:

SELECT * FROM lines 
    WHERE other_tags GLOB '*A [0-9]*'
    AND highway='motorway'

I use GLOB '*A [0-9]*' here, because in Germany every Autobahn begins with A, followed by a number (like A 73).

There is a column called other_tags with information about the motorway part:

"bdouble"=>"yes","hazmat"=>"designated","lanes"=>"2","maxspeed"=>"none","oneway"=>"yes","ref"=>"A 73","width"=>"7"

If you look closer there is the part "ref"=>"A 73".

I want to extract the A 73 as the name for the motorway.

How can I do this in sqlite?

Answer 1

If the format doesn't change, that means that you can expect that the other_tags field is something like %"ref"=>"A 73","width"=>"7"%, then you can use instr and substr (note that 8 is the length of "ref"=>"):

SELECT substr(other_tags,
              instr(other_tags, '"ref"=>"') + 8,
              instr(other_tags, '","width"') - 8 - instr(other_tags, '"ref"=>"')) name
FROM lines 
WHERE other_tags GLOB '*A [0-9]*'
  AND highway='motorway'

The result will be

name
A 73