Text/String Manipulation ISQL
Hello again, First of all, thanks to all who replied to my questions about exporting ACE reports to a filename based on a variable. The general consensus was to pipe the output to a unix shell script and do it from there. Now I have another couple of questions, so hopefully someone can give me some advice or pointers on these: 1: Text/String manipulation with Informix SE and ISQL I am using SE 5.00.4 and ISQL 4.10 on an Intergraph 2020 (Unix) and need to do some text manipulations. I'm trying to match address data from 2 different tables, both tables have a street column which is a character field and contains, yes you guessed it, a street name! The problem lies in that in one table the data has the street name with a capitalized first letter(s) and then either 'Street', 'Road', 'Crescent' etc, for example: Ashington Avenue Croagh Patrick Park The other table has the streets all in capitals and with abbreviation that I would like to expand so I can match the two, for example: ASHINGTON AVE CROAGH PATRICK PK I would like to be able to 'de-capitalize' (if there is such a word!) the text, keep the first letter of every word/string in caps, and then expand abbreviations such as AVE to Avenue, ST to Street etc. I've tried looking through the manual for string manipulation, but apart from finding the length and printing the first x characters or whatever, I haven't come up with much. I have a feeling that I can't do this kind of manipulation with ISQL and will probably need 4GL - Am I correct here, could 4GL do this kind of text manipulation or not, maybe someone who uses it could comment? It has been suggested that I export the data to ascii files and manipulate it this way, but I'm going to have 00's of 000's of records and would like to avoid this. 2: Could anyone recommend any good third party books/documents on Informix that would provide examples of the kinds of applications that can be achieved with SQL/4GL Thanks for any help that anyone can provide, Cheers, Alastair J. Small Tel: +353-1-676-0666 duQuesne Limited Fax: +353-1-676-8417 4 Merrion Square Email: asmall2@duquesne.ie Dublin, Ireland