Help needed for definition query syntax to trim off leading 0s in text field.

1080
3
05-20-2020 10:01 AM
by Anonymous User
Not applicable

Hi. I keep getting errors - This is the syntax directly from ESRI:

TRIM(BOTH | LEADING | TRAILING trim_character FROM string_exp)

I have tried: 

TRIM(LEADING '0' FROM "TRAIL_NO")

TRIM(LEADING '0' FROM '0%')

both of these error with 

Invalid SQL syntax [ORA-00920: invalid relational operator]

I have also tried many other iterations and have received errors such as missing expression, etc.

What is the correct syntax to remove leading 0s from a text field?  Please help, thanks!!

Also odd - one of the errors showed [STATE_ID = 27] and I do not have a state id field in my attribute table.

Thanks!!!!

0 Kudos
3 Replies
Bo_King
Occasional Contributor III

Hi Justine

I can only point you to the Help page, and since you've tried that, I am going move this post over to the Mapping page where you will hopefully find an answer.

Regards,

Bo 

#sql query builder#sql functions#sql statement#trim#layerquery

0 Kudos
MichaelTownshend
New Contributor III

Good Afternoon,

Have you found a resolution to this?  I am only finding the same syntax documentation as you noted above.  I have not found any working examples of how to use TRIM as all instances of the question I've found in the Esri Community pages have been left unanswered. 

0 Kudos
StevenTouzel
New Contributor II

Hello Justine, I think the problem with your query is that it needs a SQL operator like (=, <, >,...). Your TRIM expression looks fine. Here's an example that'll return all rows with an empty OWNER_NAME after the leading and trailing white space is removed:

TRIM(BOTH ' ' FROM OWNER_NAME) = ''

However, if you wanted to recalculate a field and remove the leading '0' you could use python like this:

def removezero(field):
    if (field[0] == "0"):
        return field[1:]
    else: 
        return(field)

 

 

0 Kudos