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

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

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

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

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?

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?