Re: Help: concatenating/trimming strings in SQL

On Mar 1, 12:26pm, Low Chee Meng wrote: } Subject: Help: concatenating/trimming strings in SQL } 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 } 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 (cmlow@iti.gov.sg) (fax: 65-777-3043) }-- End of excerpt from Low Chee Meng No! This is a long standing feature request of mine. Allowing expressions as subscripts to a character field and a clipped function as per 4GL in both SQL and SPL. I've had no luck in the last three years but maybe the time is right. I would also add a request for the ability to upshift an index so that the character field can contain mixed case but the index contains uppercase. This would solve many problems with lookups on names. Maybe this was what Jonathon was referring to recently in V8. Please raise this with Informix directly and hopefully it will finally rise to the top of the pile. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!