Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

zero padding in teradata sql

Tags:

sql

teradata

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?

like image 379
user3634687 Avatar asked May 14 '14 01:05

user3634687


Video Answer


1 Answers

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)

like image 123
dnoeth Avatar answered Nov 15 '22 08:11

dnoeth