CODEWITHSUNDEEP
regexstringsqliteopenstreetmapspatialite
Stefan
Stefan

Reputation: 1543

SQLite extract string from text in column

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?

Upvotes: 3

Views: 6400

Answers (2)

PlusInfosys
PlusInfosys

Reputation: 3446

Check with following condition..

  1. other_tags like A% -- Begin With 'A'.
  2. abs(substr(other_tags, 3,2)) <> 0.0 -- Substring from 3rd character, two character is number.
  3. 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'

Upvotes: 0

antonio
antonio

Reputation: 18242

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

Upvotes: 4

Related Questions