SQLite - Extract substring between delimiters for REPLACE function

Question

I have a column field: location. I need to extract the string between the first and second delimeter ('/').

I already have a column name where I ltrim to the first '/'. I've tried to create a similar query with a combination of rtrim, replace, substr as my source column to no avail. Here is what my data looks like. I want to extract AML, for example. Right now, there are only three options (value1, value2, value3) between the first and second delimiters, but there could be more later.

Attribute data
----------+--------------------------------------------------------------------------------------------------------------------
 Field    | First value
----------+--------------------------------------------------------------------------------------------------------------------
 location | './AML/Counties/*****************kyaml_20190416_transparent_mosaic_group1.tif'
 name     | 'kyaml_20190416_transparent_mosaic_group1.tif'
----------+--------------------------------------------------------------------------------------------------------------------

What is the best way of creating my column source with the value from location?

Output should be like this:

Attribute data
----------+--------------------------------------------------------------------------------------------------------------------
 Field    | First value
----------+--------------------------------------------------------------------------------------------------------------------
 location | './AML/Counties/****************kyaml_20190416_transparent_mosaic_group1.tif'
 name     | 'kyaml_20190416_transparent_mosaic_group1.tif'
 source   | 'AML'
----------+--------------------------------------------------------------------------------------------------------------------

score 2 · Answer 1 · answered Nov 13 '19 at 18:15

2

With substr() and instr():

select *,
  substr(
    substr(location, instr(location, '/') + 1),
    1,  
    instr(substr(location, instr(location, '/') + 1), '/') - 1
  ) as source
from data

See the demo.

answered Nov 13 '19 at 18:15

forpas

160,666
10
38
76

My first step in the project is to create a shapefile (could be JSON) index from a list of files in a csv. The output only has the geometry and the `location` field. I use `ogrinfo box_tiles.shp -sql "ALTER TABLE ADD name character(100)`, same for `source character(20)"`. Your method might work better because the the only fields I really want are `name` and `source`. I could use `ogr2ogr` with your `select` statement to incorporate both `subtr ...` to create my two fields. Only difference is this creates a new file instead of editing one. Both ways work. – Ian Horn Nov 13 '19 at 18:56
Strange that no one seems to need this. I tend to look at data in large Excel spreadsheets through “SQLite glasses” to catch careless errors. Today I was investigating the suspicion that a `0` had been forgotten when I stumbled across a representative of `24.%d` under masses of `240.%d`. I wanted to be able to quantify this, and I was able to thanks to your solution! I hope it gets more attention in the future! – Wolf Feb 01 '22 at 13:34

score 0 · Answer 2 · answered Mar 14 '20 at 21:50

I used forpas query to modify my query. Here is my final query

ogrinfo box_tiles.shp -dialect SQLITE -sql \
 "UPDATE box_tiles SET source = \
  substr(\
    substr(location, instr(location, '/') + 1), 1, \
    instr(substr(location, instr(location, '/') + 1), '/') - 1)"

SQLite - Extract substring between delimiters for REPLACE function

2 Answers2