Re: Leading zeros in sql

Peter Lancashire <Peter.Lancashire.PL1@bayer.co.uk> wrote: >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 > Just picking a nit, but shouldn't that be 's/|//g' in case there's more than two fields? >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/ ------------------------------------------------------- "You're missing the point," said the fork to the spoon.