Pattern or regular expression in Access

Question

In MS Access I have the following query to remove all the 0's of the left of the column [IDENTIFIER_1], but I have to define each case (If have 10 zeros on the left then make a substring from the 11 character to the end, If have 9 zeros on the left then make a substring from the 10 character to the end and so on....)

The query I have is:

SELECT YEAR(a.[First Date]) AS [N Year], Month(a.[First Date]) AS [N Month],

  IIF(    inStr(a.[IDENTIFIER_1],  '0000000000')=1, MID(a.[IDENTIFIER_1],11,LEN(a.[IDENTIFIER_1])) ,        

  IIF(    inStr(a.[IDENTIFIER_1],  '000000000')=1, MID(a.[IDENTIFIER_1],10,LEN(a.[IDENTIFIER_1])) , 

  IIF(    inStr(a.[IDENTIFIER_1],  '00000000')=1, MID(a.[IDENTIFIER_1],9,LEN(a.[IDENTIFIER_1])) , 

  IIF(    inStr(a.[IDENTIFIER_1],  '0000000')=1, MID(a.[IDENTIFIER_1],8,LEN(a.[IDENTIFIER_1])) , 

  IIF(    inStr(a.[IDENTIFIER_1],  '000000')=1, MID(a.[IDENTIFIER_1],7,LEN(a.[IDENTIFIER_1])) ,     


 IIF(    inStr(a.[IDENTIFIER_1],  '00000')=1, MID(a.[IDENTIFIER_1],6,LEN(a.[IDENTIFIER_1])) , 


 IIF(    inStr(a.[IDENTIFIER_1],  '0000')=1, MID(a.[IDENTIFIER_1],5,LEN(a.[IDENTIFIER_1])) , 

 IIF(    inStr(a.[IDENTIFIER_1],  '000')=1, MID(a.[IDENTIFIER_1],4,LEN(a.[IDENTIFIER_1])) ,   


 IIF(    inStr(a.[IDENTIFIER_1],  '00')=1, MID(a.[IDENTIFIER_1],3,LEN(a.[IDENTIFIER_1])) ,


 IIF(    inStr(a.[IDENTIFIER_1],  '0')=1, MID(a.[IDENTIFIER_1],2,LEN(a.[IDENTIFIER_1])) , a.[IDENTIFIER_1]


    )    )    )    )    )    )    )    )    )    ) 

    FROM Table1 as a;

but I wonder if there is a form like in java that you can make some pattern or regular expression, avoiding all that code. Something like a Pattern [0]+ or something that replace that code. I also try to do something like this: like '[0]+[1-9]' but I couldn't find a way to make it work.

Examples of IDENTIFIER_1 can be: 000PP2003 or 0006760007 and I need that they change to PP2003 and 6760007. It could be the case that some nulls or empty appear.

Check [this post](https://social.msdn.microsoft.com/Forums/Lync/en-US/fb0145bc-a9a9-48ca-989a-a1a678b29c12/how-to-remove-zero-from-the-begining-of-a-string-in-access-by-query-?forum=accessdev) — Wiktor Stribiżew, Aug 21 '18 at 10:37
Does `IDENTIFIER_1` contain only digits? Can you have any rows where it is Null or contains an empty (zero-length) string? — HansUp, Aug 21 '18 at 16:26
It can contain also letters. Examples of IDENTIFIER_1 can be: 000PP2003 or 0006760007 and I need that they change to PP2003 and 6760007. It could be the case that some nulls or empty appear. — user3084383, Aug 24 '18 at 11:37

Krish · Answer 1 · 2018-08-24T11:47:51.943

1

You can use val function. Read Here how val can be useful to your scenario.

something like..

SELECT 
    YEAR(a.[First Date]) AS [N Year], 
    Month(a.[First Date]) AS [N Month], 
    val(a.[IDENTIFIER_1]),
    a.[IDENTIFIER_1]
FROM Table1 as a;

EDIT: for pattern replace see this Post which could be useful with this pattern ^[0]*

edited Aug 24 '18 at 11:47

answered Aug 21 '18 at 10:50

Krish

5,917
2
14
35

Thank you. It work very well when they are only numbers but It give me 0 when there are some letters. – user3084383 Aug 24 '18 at 11:41
@user3084383 [See This answer](https://stackoverflow.com/questions/51932267/msaccess-using-a-wildcard-in-the-replace-function/51946646#51946646) – Krish Aug 24 '18 at 11:48

Pattern or regular expression in Access

1 Answers1