Truncate a Shapefile field with SQL and ogr2ogr

Question

I want to create a new attribute which is the first 8 characters of another, using ogr2ogr. To my surprise, this didn't work:

ogr2ogr -f "ESRI Shapefile" a.shp b.shp -sql "select FID,left(FLOORKEY,8) AS BLDGKEY,*"
ERROR 1: SQL Expression Parsing Error: syntax error, unexpected LEFT. Occured around :
select FID,left(FLOORKEY,8) AS BLDGKEY,*
           ^

Choosing the sqlite dialect didn't help either:

ogr2ogr -f "ESRI Shapefile" a.shp b.shp -dialect SQLITE -sql "select FID,left(FLOORKEY,8) AS BLDGKEY,*"
ERROR 1: In ExecuteSQL(): sqlite3_prepare(select FID,left(FLOORKEY,8) AS BLDGKEY,*):
  near "(": syntax error

What am I missing? It's GDAL 1.11.0, released 2014/04/16.

Answer 1

The GDAL SQL dialect is a subset of select SQL (source). It looks like left isn't part of those functions implemented. Neither is it implemented in the sqlite core functions.

Instead try the sqlite substr(X, Y, Z) function (credit to this SO answer). Your query would become:

select FID, substr(FLOORKEY, 0, 8) AS BLDGKEY,* from b

And the dialect should remain SQLITE.