SQL commands for calculating a field with python

[RichardHowe]

I'm currently trying to write a python notebook to live in AGOL that will calculate a field based on other fields.

For whatever reason, the expression has to be written in SQL

calc_expression=[{"field": "Fieldname", "sqlExpression" : ""}]

Is there a list anywhere of which SQL commands will work? From experimenting I can see that CONCAT works fine, but I have a need to add leading zeros to a number and the RIGHT, LPAD and FORMAT commands all throw errors

[ChrisUnderwood · Accepted solution]

Hello @RichardHowe , does something like this help ?

https://doc.arcgis.com/en/arcgis-online/reference/sql-agol.htm

[RichardHowe]

@ChrisUnderwood  Very useful Thank you! 🙂

Seems I'm not going to be able to pad with leading zeros then, unless you have any clever tricks?

[MobiusSnake]

Why not do a query() to get all the records you need to edit, edit the features using regular Python functions, then use edit_features() to post the edits?

[ChrisUnderwood]

@RichardHowe  what data Type is the Field you want to pad, and what is a typical item of data that is to be padded ?

[RichardHowe]

I'm writing a script to generate an ID that will run once offline data is sync'd back to the hosted feature layer. I'm using ObjectID as the initial numeric to concatenate with a custom string prefix.

I seem to have got around it by querying for length of ObjectID and padding accordingly

Thanks for your help