Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Question Unanswered: want to remove only leading zeros

    empno

    00009212as.10
    00871.qw
    0wasd1.0

    i want output to be
    empno
    9212as.10
    871.qw
    wasd1.0

    output should be without leading zeroes...pls help

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What did you do? You asked yesterday how to add leading zeros???
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,064
    Provided Answers: 11
    Air code:

    Code:
    Function StripLeads(strStart As String, strStrip As String) As String
    Dim strWork As String
    
    strWork = strStart
    
    Do Until Left(strWork, 1) <> Left(strStrip, 1)
    
    strWork = Right(strWork, Len(strWork) - 1)
    
    Loop
    
    StripLeads = strWork
    This should work.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #4
    Join Date
    Aug 2009
    Posts
    73
    Can we do it in SQL query rather than writing a function

  5. #5
    Join Date
    Aug 2009
    Posts
    73
    @Tedd i am trying to write a sql query ....can this be done without function??

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,469
    Provided Answers: 10
    Why do you not want to use a function
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  7. #7
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,064
    Provided Answers: 11
    I cannot see a way to do this without using a function, because you need to change the value in a field. Functions are what do this.

    You need to write a custom function because you need to loop through the values from left to right until you find a character that isn't zero. I did think about crafting something using the InStrRev function within a query, but that will knacker any valid values that contain non-leading zeroes. Jet SQL does not (so far as I know - I might be wrong) handle loops, so you need to execute the looped processing elsewhere.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •