topic Re: Help needed for definition query syntax to trim off leading 0s in text field. in Mapping Questions

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

Anonymous User — Wed, 20 May 2020 17:01:14 GMT

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!!!!

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

Bo_King — Wed, 20 May 2020 17:48:14 GMT

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,

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

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

MichaelTownshend — Tue, 17 Aug 2021 18:59:05 GMT

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.

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

StevenTouzel — Fri, 29 Oct 2021 16:42:20 GMT

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)

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

JMCM — Sat, 13 Apr 2024 00:26:09 GMT

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