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?

score 4 · Answer 1 · answered Oct 21 '16 at 11:38

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

score 0 · Answer 2 · edited Jul 16 '20 at 22:37

Check with following condition..

other_tags like A% -- Begin With 'A'.
abs(substr(other_tags, 3,2)) <> 0.0 -- Substring from 3rd character, two character is number.
length(other_tags) = 4 -- length of other_tags is 4

So here is how your query should be:

SELECT * 
FROM lines
WHERE other_tags LIKE 'A%'
  AND abs(substr(other_tags, 3,2)) <> 0.0
  AND length(other_tags) = 4
  AND highway = 'motorway'

SQLite extract string from text in column

2 Answers2