SQLite extract string from text in column
<p>I have a Spatialite Database and I've imported OSM Data into this database.
With the following query I get all motorways:</p>

<pre><code>SELECT * FROM lines 
    WHERE other_tags GLOB '*A [0-9]*'
    AND highway='motorway'
</code></pre>

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

<p>There is a column called <code>other_tags</code> with information about the motorway part:</p>

<pre><code>"bdouble"=&gt;"yes","hazmat"=&gt;"designated","lanes"=&gt;"2","maxspeed"=&gt;"none","oneway"=&gt;"yes","ref"=&gt;"A 73","width"=&gt;"7"
</code></pre>

<p>If you look closer there is the part <code>"ref"=&gt;"A 73"</code>.</p>

<p>I want to extract the <code>A 73</code> as the name for the motorway.</p>

<p>How can I do this in sqlite?</p>