Subject | Safe way to Concatenate String |
---|---|
Author | t.s. |
Post date | 2007-10-22T08:25:34Z |
Dear all,
In one of our stored procs, we have to generate a [document_code] field
by concatenating several other fields together.
...
document_code = project_code || '-' | revision_code
|| '-' ||employee_code || '-' || last_date;
...
For standardization purposes, all 'code' fields in this database is
mapped to a domain as VARCHAR(80). Trouble starts when the total length
of the resulting string is larger than 80 chars, with the following
error message: "Arithmetic overflow or division by zero occured.
arithmetic exception, numeric overflow, or string truncation.".
Assuming that we want the first 80 characters of the long string, and
don't mind chopping off the rest, what's the best way to do this in
Firebird ?
The alternative that crossed my mind is something like this :
...
declare variable temp = varchar(320); //4 fields, 80 each
...
temp = a||b||c||d; //concatenate all 4 fields into temp
document_code = substring(temp from 1 for 80);
...
but it seems a bit... um... clunky :)
Thank you very much in advance,
regards,
t.s.
In one of our stored procs, we have to generate a [document_code] field
by concatenating several other fields together.
...
document_code = project_code || '-' | revision_code
|| '-' ||employee_code || '-' || last_date;
...
For standardization purposes, all 'code' fields in this database is
mapped to a domain as VARCHAR(80). Trouble starts when the total length
of the resulting string is larger than 80 chars, with the following
error message: "Arithmetic overflow or division by zero occured.
arithmetic exception, numeric overflow, or string truncation.".
Assuming that we want the first 80 characters of the long string, and
don't mind chopping off the rest, what's the best way to do this in
Firebird ?
The alternative that crossed my mind is something like this :
...
declare variable temp = varchar(320); //4 fields, 80 each
...
temp = a||b||c||d; //concatenate all 4 fields into temp
document_code = substring(temp from 1 for 80);
...
but it seems a bit... um... clunky :)
Thank you very much in advance,
regards,
t.s.