Generate a Alphanumeric ID

[ajaypratap]

Hello Folks,

I have to create logic to generate 15 character alphanumeric id in this format during INSERT SQL:

1. First three character would be static, say "OFFR"
2. Next 6 character would be the date in one of the table
3. Last last five character would be the number form 1 to 99999

For example first inseted row will have ID as OFFR11021600001,next can be OFFR13021600002 .

[sakthikrr]

Hi,

Please refer the below link on generating the Sequential numbers Teradata.

https://forums.teradata.com/forum/database/best-way-to-generate-the-sequential-numbers-csum-or-ident...

You can concatenate the static text 'OFFR' & date along with that.

[srivigneshkn]

I was able to manipulate with the following statement.

 select 'OFFR'||insertdt||trim(row_number() over (order by 1 desc)) from Tablename.

1. 'OFFR' is the static character

2. Insertdt is the date from your table.

3. Row_Number for the identity column.

This would give you the desired output.

If you want to have additional zeros you can pad zeros before the rownumber statement as well.

Regards,

Srivignesh KN

[ajaypratap]

Thanks Guys!!!!!