7

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?

user3634687
  • 73
  • 1
  • 1
  • 3

1 Answers1

24

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)

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • SUBSTRING('000000000' FROM CHAR_LENGTH(string_col)+1) || string_col worked for me. Thanks! – Joshua Stafford Jul 02 '14 at 18:13
  • 1
    @dnoeth: Can you explain the syntax `AS FORMAT '9(9)'`? – Jubbles Oct 08 '14 at 00:30
  • 4
    @Jubbles: That's Cobol-style, `9` means include leading zeroes and `(9)` repeats the previous formatting character 9 times, so it's the same as `999999999`. See http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1143_111A/ch09.051.40.html#ww13915817 – dnoeth Oct 08 '14 at 06:10
  • @dnoeth how does the first line work. If the numeric_col only has 3 digit, it will fill rest of 6 digits with leading zeros? – Vinod May 15 '20 at 01:07
  • 1
    @Vinod , Yes, it applies a formatting rule while casting the numeric value as a string. – dnoeth May 15 '20 at 07:42