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

score 3 · Accepted Answer · answered Sep 15 '15 at 10:12

3

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.

answered Sep 15 '15 at 10:12

Fionnuala

90,370
7
114
152

Wow, it also solves my second problem, because I wanted to trim "002345/200003" to "2345" and the function trims it to "2345" :) – maro Sep 15 '15 at 10:30
1

Be careful with Val `ab 123` = 0, but `123 ab` = 123 – Fionnuala Sep 15 '15 at 10:32
as mentioned by Fionnuala.. you have to be careful using val function. hence i asked the data type. In any case if your str_col has a non numeric char, val function will fail & return only numeric before the first non numeric value is found. – Krish Sep 15 '15 at 10:34
Thanks. I have all data in the same format, mostly numbers but some of them have a slash /. – maro Sep 15 '15 at 10:50

Krish · Answer 2 · 2015-09-15T11:09:46.377

vba.Instr won't match by pattern. If your data column contains letters or any string and you still want to trim leading zeros, you could go for regular expressions.

Here a sample code:

Public Function FN_TRIM_LEADING_ZEROS(iValue As String) As String
    Const mExpr As String = "^[0]*" 'all leading 0s to match

    Dim mOBJ As Object
    Set mOBJ = VBA.CreateObject("vbscript.RegExp")

    mOBJ.Pattern = mExpr
    mOBJ.ignorecase = True
    FN_TRIM_LEADING_ZEROS = mOBJ.Replace(iValue, "")

End Function

just call the function by fn_trim_leading_zeros(Value) and it will return trimming leading 0s.

?FN_TRIM_LEADING_ZEROS("00000000subString.AnotherString")
subString.AnotherString

or try this recursive mode:

Public Function FN_VBA_TRIM_LEADING_ZEROS(iValue As String) As String
    If VBA.Left(iValue, 1) = "0" Then
        iValue = VBA.Right$(iValue, VBA.Len(iValue) - 1)
        FN_VBA_TRIM_LEADING_ZEROS = FN_VBA_TRIM_LEADING_ZEROS(iValue)
    Else
        FN_VBA_TRIM_LEADING_ZEROS = iValue
    End If
End Function

Hope this helps someone else

This is RBAR so could be pretty slow. – Fionnuala Sep 15 '15 at 10:51 — Fionnuala, Sep 15 '15 at 10:51

score 0 · Answer 3 · answered Sep 15 '15 at 21:57

0

If all of the records in the varchar column are numeric, then you can simply multiply the string by 1.

Be wary though. Sooner or later something non-numeric will sneak in and break things by causing an invalid cast. If possible, convert the column to an integer type and leave the formatting up to the client.

answered Sep 15 '15 at 21:57

RubberDuck

11,933
4
50
95

This is an SQL Sever answer on an MS Access question. Why? – Fionnuala Sep 16 '15 at 02:43
@Fionnuala what makes you think this is a SQL Server answer? It's not. I've done this often in Access. – RubberDuck Sep 16 '15 at 02:44
Apologies then, it was varchar that made me think so, and so many MS Access questions get SQL Server answers! – Fionnuala Sep 16 '15 at 02:46
Oh. Yeah. I can't recall off the top of my head the equivalent data type. Whatever it is, I think of it as a varchar in my own mind. – RubberDuck Sep 16 '15 at 02:47

Trimming leading zeros in MS Access SQL

3 Answers3