Re: Help: concatenating/trimming strings in SQL

In article <D4rH7z.5A4@exodus.iti.gov.sg>, cmlow@iti.gov.sg (Low Chee Meng) says: > >Hi, > >We have a table (let's call it "tb1") that contains 2 fields, >one called block_no (char(5)) and one called street_name (char(30)). >At times, we need to concatenate the block_no and street_name values >into one string. > >However, doing > SELECT block_no || " " || street_name > FROM tb1 > >results in output like > 1412 University Street > 13 Royal Street > 2 Orchard Road > >whereas I would prefer output like > 1412 University Street > 13 Royal Street > 2 Orchard Road > >where the block_no is trimmed of trailing spaces before it is joined >with the street_name. > >Is there any way I can do this using SQL, perhaps with the help of >stored procedures, but without resorting to using 4GL? > >PS: Using block_no[1, length(block_no)] in the statement seems to be >disallowed by Informix. > >Thanks in advance for any help, > >Chee-Meng Low > > >-- >--------------------------------------------------------------------- >Chee-Meng Low (cmlow@iti.gov.sg) (fax: 65-777-3043) >Information Technology Institute, Singapore >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Chee-Meng, You could always save the output, and run it through AWK: #!/bin/sh isql -s database - > datafile.dat <<+ SELECT block_no || " " || street_name FROM tb1 WHERE ... + awk ' { printf("%s %s %s\\n", $2, $3, $1 ) } ' datafile > new_data.dat Just an idea... \\\\|// (o o) ==============================---o00--(_)--00o---============================ Tim Schaefer tschaefe@gate.net The Computer Business Company, Inc. http://www.gate.net/~tschaefe Coconut Creek, FL, USA INX_UTIL Tool Kit Shareware:129.91.131.22 Ub...eh...Now, see, who gets to ask the questions here? --Ross Perot =============================================================================