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