Trimming leading zeros in MS Access SQL

Question

I need to trim leading zeros in a column using MS Access SQL. I've found the topic Better techniques for trimming leading zeros in SQL Server? but

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))

doesn't work in Access. How to "translate" it to Access SQL?

I changed the function SUBSTRING to MID and PATINDEX to INSTR, but it doesn't work

MID(str_col, INSTR(1, str_col+'.', '%[^0]%'), LEN(str_col))

The data type of my column is string and all rows looks like: "002345/200003", "0000025644/21113" and I need to extract "2345", "25644".

Answer 1

Check that the zeros really exist, they may if the field is text, in which case you can use:

Val(NameOfField)

Result

Field1  ValField1
ab      0
0000123 123

If the field is numeric, you probably have a format added to the table, which is a very bad idea.