RE: Leading Zeroes
My version of dbaccess (7.30.UC2 on SCO Unix) didn't like the substring or
the "using". However, this worked:
create procedure padit( coldata int,len int)
returning char(50); define str char(50);
define n int;
let str = coldata;
let n = length (str);
let n = len - n;
while n > 0
let str = "0" || str;
let n = n - 1;
end while;
return str;
end procedure;
> -----Original Message-----
> From: David Williams [SMTP:djw@smooth1.demon.co.uk]
> Sent: Monday, October 04, 1999 3:03 PM
> To: informix-list@iiug.org
> Subject: Re: Leading Zeroes
>
> In article <7tavag$b93$1@cougar.golden.net>, Rob Drummond
> <robdSPAMBEGONE@golden.net> writes
> >In an INSERT INTO SELECT... statement I would like to have it put an
> integer
> >into a varchar field but padded with leading zeros. For example if the
> >integer field has a value of 3 it should be inserted as '000003'. Is
> there
> >some function that will do this in Informix.
> >
>
> Which Informix version? If 7 then try
>
> NOTE: I havn't check the syntax of this!
>
> USE DBACCESS NOT ISQL TO CREATE!!!
> USE DBACCESS NOT ISQL TO CREATE!!!
> USE DBACCESS NOT ISQL TO CREATE!!!
>
> create procedure padit( coldata char(50),len int)
> define str char(50)>
> let str = "&&&&&&&&&&&&&&&&&&&&&&&";
> let str = str[1,len];
> let coldata = coldata using str;
> return coldata;
> end procedure;
>
> then
>
> insert into select padit(mycol,6),....>
> will give mycol as "000006"
>
>
> >Thanks in advance,
> >
> >Rob D.
> >
> >
>
> --
> David Williams