formatting isql output
Submitted by CTHOMAS on Wed, 2008-10-01 14:06.
For someone out there in sql land this should be easy! I have retrieved a list of borrower names and barcodes (BARCODE, SURNAME, FIRST_NAMES) and want to set the columns in the output file so they are shortened to 30 spaces per column instead of the default settings. This is to enable me to play around with it in Excel. Any suggestions how I can do this? Thanks
Formatting SQL query
You can do this using the Sybase substring command. The figure is the starting point in the string and the second number is the number of characters to be displayed.
select BARCODE,
substring(SURNAME,1,30) SURNAME,
substring(FIRST_NAMES,1,30) FIRST_NAMES
from BORROWER
where BORROWER_ID between 1000 and 1050
When you use substring then the attribute names for those columns are not displayed as headings. By adding a name at the end these can be added back (as above)
However isql is limited in what you can do formatting-wise. More can be done by using Talis Decisions or PERL scripts. There is a TDN article about creating CSV files called Creating a CSV file of borrower information
Thanks
Brian Crampton
Developer, Talis