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.

1 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.

  • Ah, thank you! I had actually tried SUBSTR, but not in the right combination with -dialect. Mar 8, 2016 at 2:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.