Converting numeric fields into strings with leading zeros

Question

I'm trying to define an action in QGIS using on of my fields but I have some problems with its format.

I have a numeric string like '5' and I need to have a format like '005'. I've tried function tostring("myfield") but I am not able to define the format that I want.stri

Is there any way to do something like tostring(myfield,"000")?

What I'm looking for is a function like this one: http://office.microsoft.com/en-us/excel-help/text-HP005209313.aspx?CTT=5&origin=HP005204211

Answer 1

The solution is:

'000'   ||  tostring("myfield")

(a variable = 'variable') + (string concatenation = ||) + (field = "myfield")

5 -> 0005
50 -> 00050
500 -> 000500

combining variables

'hello'   || ', ' ||  tostring("myfield")

5 -> hello, 5

'hello'   || ', ' ||  tostring("myfield") || ', goodbye'

5 -> hello, 5, goodbye

combining fields

tostring("myfield") || ', ' || tostring("otherfield")

combining other thinks, like geometry

For example, for a point, the x and y coordinates -> (x, y)

'('   ||   $x  ||   ','   ||  $y  || ' )'

and you can format the result

right(  ('000'   ||  tostring(  "myfield" )), 4)

5 -> 0005
50 -> 0050
500 -> 0500

etc.

Answer 2

Another, and possibly more compact, option using lpad() (Left Pad) under String Functions which requires three arguments: the field, desired length, and padding character

lpad("MyField", 3, '0')

Answer 3

In QGIS 1.8 I've used this one because there wasn't right() function:

substr(('000' || tostring("myfield")), (length(('000' || tostring("myfield")))-2), 3)

And I've obtained 005