Solved How do I add a zero to a number within a databricks Table using SQL (1 Viewer)

[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);

 

[Pat Hartman]

This is an Access forum so CJ gave you Access syntax. If you need syntax for a different database engine, it is always best to specify that. I'm not sure how the Concat() function works. Does it fill the field with leading zeros depending on the definition of the field length and the contents of the field?