Re: Leading zeros in sql

LINDA SWARTZ wrote: > > Hi, > Could anyone tell me how to put leading zeros on the beginning of a = > char field? This is necessary to send to another site. Also could you = > tell me how to unload a table to a flat file with no delimiters? This is = > on a NCR Unix box in informix 5.05 using sql. > I would appreciate any help you can give me. > Linda =20 > > =20 Do you use Informix 4GL? If so, you can use that to put leading zeros on like this: DEFINE newchar CHAR(12) DEFINE oldchar CHAR(9) LET newchar = "000", oldchar If you don't use 4GL you could achieve the same effect with the ACE report writer in Informix SQL. The database engine won't do this for you. You'd need version 7.x, which can do string concatenation in SQL. To unload without delimiters (why?) you could pass the file produced by UNLOAD through the Unix sed stream editor to remove the delimiters like this: sed 's/|//' <infile >outfile If you have embedded (and escaped) | delimiters in your data you need to get more sophisticated. Removing the delimiters will prevent you finding nulls in the unload file as they are represented by two consecutive delimiters. Are you actually trying to produce a file in a fixed-column format? If so, unload will not help much because it writes columns in variable-width fields. You should be able to do what you need with 4GL or ACE (if the line width is not too great - I forget the maximum limit). -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions AND the release notes. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/