zero padding in teradata sql

Question

Table A
Id varchar(30)

I'm trying to re-create a logic where I have to use 9 digit Ids irrespective of the actual length of the Value of the Id field.
So for instance, if the Id is of length 6, I'll need to left pad with 3 leading zeros. The actual length can be anything ranging from 1 to 9.

Any ideas how to implement this in Teradata SQL?

Answer 1

If the actual length is 1 to 9 characters why is the column defined as VarCar(30)?

If it was a numeric column it would be easy:

CAST(CAST(numeric_col AS FORMAT '9(9)') AS CHAR(9))

For strings there's no FORMAT like that, but depending on your release you might have an LPAD function:

LPAD(string_col, 9, '0')

Otherwise it's:

SUBSTRING('000000000' FROM CHAR_LENGTH(string_col)+1) || string_col,

If there are more than nine characters all previous calculations will return them.

If you want to truncate (or a CHAR instead of a VARCHAR result) you have to add a final CAST AS CHAR(9)

And finally, if there are leading or trailing blanks you might want to use TRIM(string_col)