Upper and Lower Case with Informix SE 5

Afternoon Informixers... SCO 5 Informix SE 5 ISQL 4.20 (yes, I know, as old as the hills) I've recently taken over responsibility (lucky me) of an Informix SE 5 database with loads of data. We want to do case in-sensitive searches on a few fields to see if we have duplicates (name, address etc...), however, in my investigations the following restrictions apply to the current version of the engine: 1. SPL in version 5 DOES NOT support VARCHAR variables 2. SPL in version 5 DOES NOT support the TRIM() function 3. SPL in version 5 DOES NOT suppor the CLIP function 4. Concatenating two strings DOES NOT remove trailing spaces 5. SPL in version 5 DOES NOT support variable off-sets when addressing character positions using the [] notation i.e. LET CharToShift = StrVariable[$start,$finish] is NOT supported. Unfortunately, upgrading is NOT an alternative!! How does one go about doing upper and lower conversions and comparisons in this god forsaken engine?? I have already gone through the IIUG group and trawled my way through this newsgroup but nothing seems to be the right fit. You'd have thought that converting each character in the string to upper or lower case, one character at a time, would have been the answer, but due to the restrictions above, this just doesn't work! Anyone got any alternative ideas? P.S. I don't do C but I know a man who can, if external SPL is the ONLY alternative... Many thanks for reading this, and more thanks for any replies. Sean.

Chilli Fiend said: > Afternoon Informixers... > > SCO 5 > Informix SE 5 > ISQL 4.20 > > (yes, I know, as old as the hills) > > I've recently taken over responsibility (lucky me) of an Informix SE 5 > database with loads of data. > > We want to do case in-sensitive searches on a few fields to see if we > have duplicates (name, address etc...), however, in my investigations > the following restrictions apply to the current version of the engine: > > 1. SPL in version 5 DOES NOT support VARCHAR variables > 2. SPL in version 5 DOES NOT support the TRIM() function > 3. SPL in version 5 DOES NOT suppor the CLIP function > 4. Concatenating two strings DOES NOT remove trailing spaces > 5. SPL in version 5 DOES NOT support variable off-sets when addressing > character positions using the [] notation i.e. > > LET CharToShift = StrVariable[$start,$finish] > > is NOT supported. > > Unfortunately, upgrading is NOT an alternative!! > > How does one go about doing upper and lower conversions and comparisons > in this god forsaken engine?? > > I have already gone through the IIUG group and trawled my way through > this newsgroup but nothing seems to be the right fit. > > You'd have thought that converting each character in the string to > upper or lower case, one character at a time, would have been the > answer, but due to the restrictions above, this just doesn't work! > > Anyone got any alternative ideas? > > P.S. I don't do C but I know a man who can, if external SPL is the ONLY > alternative... > > Many thanks for reading this, and more thanks for any replies. Wow. What a problem to be lumbered with. :o( Have you considered adding a column to the table(s) with upshifted data and doing your case-insensitive searches on that? -- Bye now, Obnoxio Information within this post contains forward looking statements within the meaning of Section 27A of the Securities Act of 1933 and Section 21B of the S E C Act of 1934. Statements that involve discussions with respect to projections of future events are not statements of historical fact and may be forward looking statements. Don't rely on them to make a decision. The poster is not a reporting company registered under the Exchange Act of 1934. I have received a life peerage from Her Majesty, who is not an officer, minister or affiliate Labour party member. I intend to recover my loan now, which could cause the parliamentary majority to go down, resulting in losses for you. Today's Labour party has: an accumulated deficit and a reliance on loans from officers and affiliates to pay expenses. It is not an operating political party. The party is going to need financing to continue as a going concern. A failure to finance could cause the party to go out of business. This report shall not be construed as any kind of investment advice or solicitation. You can lose all your money by investing in this party.

Do you have the substring command in version 5? You should then be able to approximate the [start,finish] syntax with the substring function. You could then use one of the upper functions in the repository (returning char instead of varchar).

Chilli Fiend wrote: > Afternoon Informixers... > 'Evening. > SCO 5 > Informix SE 5 > ISQL 4.20 > > (yes, I know, as old as the hills) > Ouch. > I've recently taken over responsibility (lucky me) of an Informix SE 5 > database with loads of data. > > We want to do case in-sensitive searches on a few fields to see if we > have duplicates (name, address etc...), however, in my investigations > the following restrictions apply to the current version of the engine: > > 1. SPL in version 5 DOES NOT support VARCHAR variables > 2. SPL in version 5 DOES NOT support the TRIM() function > 3. SPL in version 5 DOES NOT suppor the CLIP function > 4. Concatenating two strings DOES NOT remove trailing spaces > 5. SPL in version 5 DOES NOT support variable off-sets when addressing > character positions using the [] notation i.e. > But can you use the regular expression notation? like "*[Aa][Bb]*" and so on? > LET CharToShift = StrVariable[$start,$finish] > > is NOT supported. > > Unfortunately, upgrading is NOT an alternative!! > > How does one go about doing upper and lower conversions and comparisons > in this god forsaken engine?? > > I have already gone through the IIUG group and trawled my way through > this newsgroup but nothing seems to be the right fit. > > You'd have thought that converting each character in the string to > upper or lower case, one character at a time, would have been the > answer, but due to the restrictions above, this just doesn't work! > > Anyone got any alternative ideas? > > P.S. I don't do C but I know a man who can, if external SPL is the ONLY > alternative... > > Many thanks for reading this, and more thanks for any replies. > > Sean. > > -- Everything works -- if you let it.

Chilli Fiend wrote: > Afternoon Informixers... > > SCO 5 > Informix SE 5 > ISQL 4.20 > > (yes, I know, as old as the hills) > > I've recently taken over responsibility (lucky me) of an Informix SE 5 > database with loads of data. > > We want to do case in-sensitive searches on a few fields to see if we > have duplicates (name, address etc...), however, in my investigations > the following restrictions apply to the current version of the engine: > > 1. SPL in version 5 DOES NOT support VARCHAR variables Only in SE (including SE 7.x) - SPL in OnLine 5 supports VARCHAR happily. If you need VARCHAR, you should change server to one that supports the type. > 2. SPL in version 5 DOES NOT support the TRIM() function No - it was added to IDS in version 7.x, possibly even 7.3x. If you need the TRIM function, upgrade to software that supports it. > 3. SPL in version 5 DOES NOT suppor the CLIP function No - does any version of SPL support it? > 4. Concatenating two strings DOES NOT remove trailing spaces It is not supposed to do so - it would be contrary to the SQL standard to do so. > 5. SPL in version 5 DOES NOT support variable off-sets when addressing > character positions using the [] notation i.e. > > LET CharToShift = StrVariable[$start,$finish] > > is NOT supported. Agreed; neither does IDS 10.00. However, IDS 7.3x and later does have the SUBSTR() function. > Unfortunately, upgrading is NOT an alternative!! Why not? > How does one go about doing upper and lower conversions and comparisons > in this god forsaken engine?? ISQL forms supports UPSHIFT and DOWNSHIFT for data entry. Don't forget; SE 5.00 was released circa 1990, and it is not fair to blame the product for not having the same features as a product released last year. If you want modern functionality, use a modern product. If you can't use a modern product, then expect archaic functionality. > I have already gone through the IIUG group and trawled my way through > this newsgroup but nothing seems to be the right fit. > > You'd have thought that converting each character in the string to > upper or lower case, one character at a time, would have been the > answer, but due to the restrictions above, this just doesn't work! Nonsense - it can be done, but I'd never recommend doing it. Slow doesn't begin to describe the process. There is (or should be) SPL available at the IIUG web site to do it - fortunately, it has largely been superseded by more recent releases of servers. If you can't find it there, I almost certainly still have copies of it languishing in an email archive. > Anyone got any alternative ideas? > > P.S. I don't do C but I know a man who can, if external SPL is the ONLY > alternative... SE 5 doesn't do external C code either - for that, you need IDS 9.x or 10.x. > Many thanks for reading this, and more thanks for any replies. If you're sure you can't upgrade despite all the reasons you found for needing to upgrade, then use the case-converted column mentioned by OTC. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/