Wednesday 14 March 2012

Removing Leading Zeros

I was talking to someone this morning who asked me if it was possible to remove the leading zeros coming from his data source. After an initial "yes of course" followed by a longer period of thought, the solution we ended up with was as follows:

replace(ltrim(replace(MyField,'0',' ')),' ','0') AS MyField

This code first uses the replace() function to replace all zeros in the field with a space and then uses the ltrim() function which removes spacing from the begining of a string only. We then use replace() again to convert any remaining spaces back to zeros.

6 comments:

  1. If it is only digits in "MyField" you should be able to use num(MyField) alt. Num(MyField, 'YourFormat') to remove the leading zero.

    ReplyDelete
    Replies
    1. Hi Vegar

      In that instance, QlikView actually stores the leading zeros still and simply formats the number to effectively hide them. This normally doesn't cause an issue for numbers though as QlikView would equate 00012345 to be the same value as 12345 anyway.


      Matt

      Delete
  2. I really like this solution. The only potential flaw is if the data contains spaces in any value. Like '000123 333'. We want '123 333' as the result, instead we will get '1230333'.

    ReplyDelete
    Replies
    1. Hi Richard
      You are correct, although there is a solution to that problem assuming you can identify a character (or sequence of characters) that is impossible to occur in the strings. For example, if star (*) wasn't possible you could use the following:

      replace(replace(ltrim(replace(replace(MyField, ' ', '*'),'0',' ')),' ','0'), '*', ' ') AS MyField

      Regards
      Matt

      Delete
  3. Hi Matthew, this looks a very elegant solution! Would this work where values have a leading '<' sign instead, for example recording '<5' as 5?

    ReplyDelete
    Replies
    1. Hi James, The short answer is yes although you really need to consider other possible values in the field. Can '<' appear anywhere else in the strings? If so do you wish to replace all of them or only when they appear at the start? Can only one '<' be at the start or is '<<' or '<<<' possible? As Richard pointed out above, are spaces possible in the field?

      Delete