[Solved] Padding Numeric Result for Concatenation/Increment
Posted: Tue Feb 20, 2018 2:47 am
Hello all!
I'm trying to create a unique reference which consists of a 3 char code, plus another 3 char code and finally a 3 digit number
I have:
Three tables 1,2 & 3, each with IDs and Codes
The tables are linked with Table 1 being the parent of Table 2 and the grandparent of Table 3
My aim is for each time a new record is created in Table 3, a unique number is generated which will take the format of "Table 1"."Code" plus "Table 2"."Code" plus a 3 digit number which auto-increments for each new record in Table 3.
So Number should look something like this:
ABC-XYZ-001
ABC-XYZ-002
ABY-XYZ-003
DEF-GHI-001
DEF-GHI-002
Each time a new record is created with the same Table 1 Code and Table 2 Code, then the 001 part of the number should increment by 1.
ABC and XYZ are VARCHAR, and I have a 1 which is the result of a Count of Table 3 IDs.
So I need a number to have 3 digits and to be able to increment it.
I can only find ways to pad the number by changing the format, which fails as soon as it is concatenated. I also need to retain as numeric,so that I can increment by 1.
Any help greatly appreciated.
I'm trying to create a unique reference which consists of a 3 char code, plus another 3 char code and finally a 3 digit number
I have:
Three tables 1,2 & 3, each with IDs and Codes
The tables are linked with Table 1 being the parent of Table 2 and the grandparent of Table 3
My aim is for each time a new record is created in Table 3, a unique number is generated which will take the format of "Table 1"."Code" plus "Table 2"."Code" plus a 3 digit number which auto-increments for each new record in Table 3.
So Number should look something like this:
ABC-XYZ-001
ABC-XYZ-002
ABY-XYZ-003
DEF-GHI-001
DEF-GHI-002
Each time a new record is created with the same Table 1 Code and Table 2 Code, then the 001 part of the number should increment by 1.
ABC and XYZ are VARCHAR, and I have a 1 which is the result of a Count of Table 3 IDs.
So I need a number to have 3 digits and to be able to increment it.
I can only find ways to pad the number by changing the format, which fails as soon as it is concatenated. I also need to retain as numeric,so that I can increment by 1.
Any help greatly appreciated.