Extract string from the first position that’s not “0” SQL

I am trying to find the first position of a value that’s not “0” in a string so that I can extract the values that come after. The strings are all length 10. The strings all start with D and then a variable amount of “0” and I need to find the first position in the string that’s not a “0”. Examples:

String      Need to extract
D009902167, 9902167
D000128677, 128677
D000004395, 4395

I first wanted to do it like this:

Mid(Stringname,InStr(2,Stringname, <>"0"))

But I’ve now read that InStr doesn’t accept <>, ! or NOT. How can I extract the string from the first position where the value isn’t “0”?

Answer

Use MID() to remove the 1st char and then add 0 to get only a numeric result (Access will do an implicit cast to integer):

SELECT MID(Stringname, 2) + 0
FROM tablename

Advertisement

Answer