Create Custom Column - Add leading 0's



  • Avatar
    Ido Darnell

    Hi Enrique Jauregui

    If I understand correctly, you can achieve this by creating a new custom column with the following query. Basically it will check how long the string value of the Int is to begin with, meaning how many characters long it is, and then add a prefix with the correct number of '0's accordingly. This query will assume that the original INT values can be only of 1, 2, 3, 4, 5 or 6 characters long to begin with. This will work if the original customer_id field is an INT or a string/text data type:


    CASE WHEN length(tostring(old_customer_id))=1 THEN '00000'+tostring(old_customer_id))

    WHEN length(tostring(old_customer_id)))=2 THEN '0000'+tostring(old_customer_id))

    WHEN length(tostring(old_customer_id)))=3 THEN '000'+tostring(old_customer_id))

    WHEN length(tostring(old_customer_id)))=4 THEN '00'+tostring(old_customer_id))

    WHEN length(tostring(old_customer_id)))=5 THEN '0'+tostring(old_customer_id))

    ELSE tostring(old_customer_id))

    END AS New_Customer_id


    Let me know if that's what you're looking for,

    Feel free to reach out if you have any other questions,

    QBeeQ - Sisense Partner of the year 2019/2020

    Comment actions Permalink
  • Avatar
    Enrique Jauregui

    Thank you.  This worked perfectly. 

    Comment actions Permalink

Please sign in to leave a comment.