Summary: concatenating/trimming strings in SQL

There seems to be 2 solutions to my problem: 1) make block_no and street_name varchar fields instead of char fields - this would require changing the database schema for tb1, which I fortunately have the luxury of doing 2) write a "trim" stored procedure that takes a varchar and returns a varchar and which contains code like if (length(str) = 1) then return(str[1,1]); elif (length(str) = 2) then return(str[1,2]); ... elif (length(str) = 20) then return(str[1,20]); ... obviously this has got to stop at some point, so this stored procedure will only work up to some finite limit, say up to 40 chars Thanks to all those who helped. Chee-Meng My original question: > 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. > -- --------------------------------------------------------------------- Chee-Meng Low (cmlow@iti.gov.sg) (fax: 65-777-3043) Information Technology Institute, Singapore >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<