Help: concatenating/trimming strings in SQL

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 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<