There are many difficult ways you can do things. Databases are
primarily about storing and retrieving data, not so concerned about
formatting issues.

I imagine all of these could be solved in a couple of seconds in
Delphi (what you are obviously using here), in Firebird you will need
to either write a UDF function to do it, or play around mixing casts
etc inside a particular Stored procedure.

CREATE PROCEDURE FORMATNUMBER
( VALUE INTEGER,
LENGTH INTEGER,
FILLWITH INTEGER
)
RETURNS
(
FORMATTEDVALUE VARCHAR(32000) -- yep, this will be a limitation
)
AS
BEGIN
FORMATTEDVALUE = CAST(VALUE AS VARCHAR(32000);
WHILE (STRLEN(:FORMATTEDVALUE) < :LENGTH) DO
BEGIN
-- STRLEN IS IN IBSQL.DLL
FORMATTEDVALUE = CAST(FILLWITH AS CHAR(1)) || :FORMATTEDVALUE;
END
SUSPEND;
END
^

etc

Untested though, there are other functions like extractyear etc that
will be of interest if you want to do it with pure PSQL.

Personally, I think it is similar to this sort of thing:
http://www.99-bottles-of-beer.net/language-firebird-sql-257.html

The only reason I can think of attempting it is to prove it could be
done. There are easier ways to do it outside the database.

Adam

--- In firebird-support@yahoogroups.com, "fkbash" <ffkammer@c...> wrote:
>
> Hi all,
>
> I'm creating a exportation data routine in my software and I need that
> the SQL statment get the data already formated from my BD.
>
> I must be able to get the data on the format below:
> - strings: formated with a specific amount of positions, if the string
> is shorter than the size need I must put blank spaces on the end of
> string;
> - date: formated following a parameter (dd/mm/yyyy, ddmmyyyy, yymmdd,
> etc);
> - number: formated with a specific amount of positions, if the number
> is shorter than the size needed I must put zeros on the begin of the
> number. The amount of digits in decimal must be a parameter and I need
> the number without decimal separator.
>
> I'll use this to do something as bellow:
>
> select formatnumber( a.integerfield, 6, 0 ) || formatstr( a.strfield,
> 10 ) || formatdate( a.datefield, 'ddmmyyyy' ) || formatnum(
> a.floatfield, 10, 2 ) from a;
>
> In the example I'll get the integerfield with 6 digits (000013), the
> strfield with 10 positions ('TEST '), the datefield formated
> following the parameter (21122005) and floatfield formated with the
> total of 10 positions and 2 decimals without decimal separator
> (12.55=0000001255).
>
> Can I do this without an UDF???
>
> I know that I do this in Oracle and MySQL, but I don't imagine how I
> can do this in Firebird without using UDF. I can use UDF, I only have
> problems that I'll need to compile the UDF for windows and linux
> plataforms and I don't have a license of Kylix.
>
> Thanks in advance
>
> Fabrício F. Kammer
> Conchal/SP - Brazil
>