A simple (though, admittedly not elegant) way would be

Select(substring(document_id from 1 for 9) ||
Case
when substring(document_id from 10 for 6) < '000009' then '00000' ||
cast(cast(substring(document_id from 10 for 6) as integer)+1 as char(1))
when substring(document_id from 10 for 6) < '000099' then '0000' ||
cast(cast(substring(document_id from 10 for 6) as integer)+1 as char(2))
when substring(document_id from 10 for 6) < '000999' then '000' ||
cast(cast(substring(document_id from 10 for 6) as integer)+1 as char(3))
when substring(document_id from 10 for 6) < '009999' then '00' ||
cast(cast(substring(document_id from 10 for 6) as integer)+1 as char(4))
when substring(document_id from 10 for 6) < '099999' then '0' ||
cast(cast(substring(document_id from 10 for 6) as integer)+1 as char(5))
else
cast(cast(substring(document_id from 10 for 6) as integer)+1 as char(6))
end

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of majstor
Sent: 2. november 2009 12:14
To: firebird-support@yahoogroups.com
Subject: [firebird-support] leading and trailin zero in select statement

Hi,

I have small problem, when I need to ad some extra text or leading or trailin zero numbers into my result of select sql statement in firebird. For now I solve this with some procedure on client app, but I need to do this with select statement.

for example I have a document ID 'X01-FDDA-001234' and next document id will be same as this just at the and on word wil be 001235! So is there any solution in firebird to solve this problem?

Thanks in advanced...