Case-Insensitive SELECT

}Can someone help me out on how to do a case-insensitive SELECT on a string? }Using version 7.1. Thanks in advance. } }-- }/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\ }/\\ Harold Sasaki }/\\ hsasaki@harold.com }/\\ http://www.harold.com/ }/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\ }/\\ This week's name that tune: }/\\ "...best that you can hope for is to die in your sleep..." }/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\/\\ I haven't seen any responses to this, but Catalin Badea posted some excellent suggestions and code for performing same within a CONSTRUCT statement that should help: }Tony Holder (Tony@robi.demon.co.uk) wrote: }: Is there any way to turn of case sensitivity for a simple query e.g. [ snip ] Catalin Badea wrote: }Yes, there is a way. } }select * from clients where cliname matches "[Ss][Mm][Ii][Tt][Hh]*" } }It will match "Smith" "SMITH" "sMiTh" etc... } }The same "trick" can be used for the CONSTRUCT by including: } after field fieldName } let value = fgl_getfldbuf() } let value = formatValue(value) } display value to fieldName } }A formatting function can easily be written: } Scan string } substitute every letter by [UPPERlower] }Caveat: you must handle actual [char] expressions that the user } may specify } }Enjoy! } } }-- };,._..___...-''^``--._ |\\, Catalin Badea }```--,,,--, `-_ .`/ ,\\ Senior Technical Architect } {\\ ` ``-_' / } Tecsys Inc. } ; ; ; `;;' } `.;. `.._ ,,,_. ``--.._. cat@tecsys.com } ``--..,) ```----...._,) include system "disclaim.4gh" } (yeah, another NewEra fiend) }Carl Uschold (carl@spock) wrote: }: There's no way to make construct do a case insensitive search...not without }: using all possible cominations of upper and lower case in the constructed }: query (ie SM* Sm* sM* and sm*) This list would becomes 2**chars long. A }: better solution would be the ablility to create a "case insensitive" index }: on a field, so that the values in the index are all upper case for query }: purposes, but the field values in the .dat file are mixed case. Then a }: query for SM* would bring back all three Smith records. Catalin Badea wrote: } }Of course THERE IS. } }The [Ss][Mm]* expression can be used to match all records beginning with }sm, SM, sM, Sm. } }The Informix Engines recognize "match-either-of-these-characters" expressions, }(e.g. [ab], [a-z], [a=0k]). } } }A formatting function can be written to change a string (e.g. smith) to }an expression (e.g. [Ss][Mm][Ii][Tt][Hh]). It can then be used inside }or after the CONSTRUCT statement. } }Sample formatting function: } }function formatExpr(source) }define } source char(100), # size as required } sourceLen smallint, } result char(200), # size as required } resultLen smallint, } chr char(1), # current Character } insideExpr smallint, # Inside an existing expression '[]' } skipNext smallint, # Escape Sequence '\\' } i smallint # loop counter } } let result = " " # all spaces } let sourceLen = length(source) } let resultLen = 0 } let skipNext = false } let insideExpr = false } } for i = 1 to sourceLen } let chr = source[i,i] } case } when skipNext # Do nothing } when insideExpr # Do nothing } when chr matches "[A-Za-z]" } let resultLen = resultLen + 4 } let result[resultLen-3,resultLen] = } "[", upshift(chr), downshift(chr), "]" } continue for } end case } } let resultLen = resultLen + 1 } let result[resultLen] = chr } case } when skipNext = true let skipNext = false } when chr = "\\\\" let skipNext = true } when chr = "[" let insideExpr = true } when chr = "]" let insideExpr = false } end case } end for } } return result }end function } } }Sample run: } display formatExpr("smith") } [Ss][Mm][Ii][Tt][Hh] } } display formatExpr("[abc] Joe") } [abc] [Jj][Oo][Ee] } } } };,._..___...-''^``--._ |\\, Catalin Badea }```--,,,--, `-_ .`/ ,\\ Senior Technical Architect } {\\ ` ``-_' / } Tecsys Inc. } ; ; ; `;;' } `.;. `.._ ,,,_. ``--.._. cat@tecsys.com } ``--..,) ```----...._,) include system "disclaim.4gh"