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!!!!
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
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.
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)
Hello, I know this thread is 4 years old, but I wanted to help future users who found this using Google search like I did.
I found the correct ArcGIS SQL syntax to be TRIM(LEADING '0' FROM TRAIL_NO) (no double quotes around the string expression variable). However, on my end, I still got an error after that.
The Python lstrip function works to remove a leading character, but I assume if you're using SQL in the first place, you want to not use Python (which is slower). I got around this by definition querying/filtering the table first so that it doesn't iterate over unnecessary rows.
Finally, for my purpose, I ended up using the Ctrl + F (find and replace) feature instead. One needs to be careful with this function, but if it applies to your situation, it works *fast*.