Solved How do I add a zero to a number within a databricks Table using SQL

[Number11]

So i need to add leading zero to a set of numbers within a table that i imported into databricks from a CSV in doing so i lost the leading zeros, the format of the number should be like this


0999
0010
0009

i have

999
10
9

i tried this


UPDATE Test table name
SET IID = '0' + IID

Resulting in

999.0
10.0
9.0

any ideas please

 

[CJ_London]

numbers do not have leading zero's if they did, they would be text. If you don't need as a number for calculations, ensure the destination field is text and your import specification is set to text for that field

If needed as a number but you want to display leading zero's use the format property - for 4 characters it would be 0000. Ideally do this in the form or report, not the table as it is a bad idea to do any formatting in a table because it masks the true underlying value

if you need to modify existing numeric data to text, add a new column (text) to the table then run an update query along the lines of

UPDATE myTable
SET newfieldname=format(oldfieldname,"0000")

then you can delete the old field

 

[Number11]

numbers do not have leading zero's if they did, they would be text. If you don't need as a number for calculations, ensure the destination field is text and your import specification is set to text for that field

If needed as a number but you want to display leading zero's use the format property - for 4 characters it would be 0000. Ideally do this in the form or report, not the table as it is a bad idea to do any formatting in a table because it masks the true underlying value

if you need to modify existing numeric data to text, add a new column (text) to the table then run an update query along the lines of

UPDATE myTable
SET newfieldname=format(oldfieldname,"0000")

then you can delete the old field

Ok Thanks that does work on databricks sql

 

[Number11]

Ok Thanks that does,nt work on databricks sql

 

[Number11]

Got it working the correct code is:

UPDATE Test table name
SET IID = CONCAT('0', IID);