SQLite从列中的文本中提取字符串

Question

我有一个Spatialite数据库，并且我已经将OSM数据导入到这个数据库中。通过以下查询，我得到了所有的高速公路：

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

我在这里使用GLOB '*A [0-9]*'，因为在德国，每条高速公路都以A开头，后面跟着一个数字(如A73)。

有一个名为other_tags的列，其中包含有关高速公路部分的信息：

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

如果你仔细看，这是"ref"=>"A 73"的一部分。

我想提取A 73作为高速公路的名称。

我如何在sqlite中做到这一点？

Answer 1

如果格式没有改变，这意味着您可以预期other_tags字段类似于%"ref"=>"A 73","width"=>"7"%，那么您可以使用instr和substr (请注意，8是"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'

结果将是

name
A 73

Answer 2

检查以下条件..

1. other_tags like A% --以'A'.
2. abs(substr(other_tags, 3,2)) <> 0.0开头--从第三个字符开始的子串，两个字符为number.
3. length(other_tags) = 4 -- other_tags的长度为4

下面是你的查询应该是什么样子：

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'