Adding Leading Zeros

Sanket_ShahSanket_Shah Registered User
Hello- I want to add leading zeros to a varchar field with length of 13. The following is the generally accepted syntax Select right('0000000000000' + PROD_ID, 13) FROM PRODUCTS The error I am getting is: [SELECT - 0 row(s), 0.000 secs] [Error Code: 0, SQL State: 42725] ERROR: operator is not unique: "unknown" + varchar Which is not helpful. Can anyone please help?

Comments

  • Hi Sanket, To have leading zeros you could use the to_char function select to_char(prod_id, '0000000000000') from products; Hope this helps, Thanks, Eugenia
  • Shaun_CarneyShaun_Carney Employee, Registered User, VerticaExpert
    Hi Sanket, I believe you are interested in the LPAD function: dbadmin=> select lpad('123',13,'0'); lpad --------------- 0000000000123 (1 row) If you prefer to use concatenation, the following adjustments should help with your original statement: dbadmin=> select right('0000000000000'||123456,13); right --------------- 0000000123456 (1 row)
  • Sanket_ShahSanket_Shah Registered User
    Thanks..LPAD WORKS

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file