Re: Non-case sensitive queries

In article <dennisp.766599337@infmx>, dennisp@informix.com (Dennis Pimple) writes: |> orrom@itnet.demon.co.uk (Howard Orrom) writes: |> |> >My programming colleagues ( Informix fans ) asked if I could put out a call |> >for advice / help. |> >In a character column, text can be entered in upper or lower case - |> >eg. John Smith, JOHN SMITH or jOHN sMITH. |> >Now, if a search is done for J Smith, how do you ensure that all of the |> >examples above are returned ? |> >( Our solution is to have a separate column into which a standardised form |> >of the text is placed, leaving the 'users' column for use in printed |> >reports etc) |> |> If speed is important and disk space is available, I think yours is a |> good solution. If the reverse is true, I wrote a 4gl function that |> would convert your query to: |> |> MATCHES "[Jj][Oo][Hh][Nn] [Ss][Mm][Ii][Tt][Hh]" |> |> which is guaranteed to be slow. If your're interested, let me know and |> I'll dig it out and post it. |> |> >Anyone got any better ideas ? Answers on 20-pound notes please to .... |> |> In the USA, that would be a HEAVY note, not an EXPENSIVE one ... |> |> ============================================================ |> Dennis J. Pimple Informix Software Inc |> Senior Consultant Denver Colorado USA |> dennisp@informix.com Voice:303-850-0210 Fax:303-779-4025 Another way, which just came to me, would be to convert the users input into a standard nice format before writing to the table. And then convert the query string into the same nice format before reading the table. You could decide that the correct format to use was John Smith, with the initial letters of the name forced into upper case and the rest of the word forced into lower case: If the user knew this was required when querying the table then you would not have any problems with having to alter CONSTRUCT strings. So here is the function: MAIN DEFINE p_name_in, p_name_out CHAR(30) WHILE TRUE PROMPT "Enter your name (0=Exit): " FOR p_name_in IF p_name_in = "0" THEN EXIT WHILE END IF LET p_name_out = conv_name(p_name_in) DISPLAY p_name_out END WHILE END MAIN FUNCTION conv_name(pass_name) # Example Input: JOHN j. sMitH # Example Output: John J. Smith DEFINE pass_name, ret_name CHAR(30) DEFINE pos SMALLINT, found_word SMALLINT, a_char CHAR LET ret_name = " " LET found_word = FALSE FOR pos = 1 TO LENGTH(pass_name CLIPPED) IF pass_name[pos] != " " THEN # If found a non-space IF NOT found_word THEN # if new word then LET a_char = UPSHIFT(pass_name[pos]) # upshift the initial LET found_word = TRUE # ELSE # else LET a_char = DOWNSHIFT(pass_name[pos]) # downshift letter END IF # ELSE # else LET a_char = " " # just use a space LET found_word = FALSE # finished word END IF LET ret_name[pos] = a_char END FOR RETURN ret_name END FUNCTION { Enter your name (0=Exit): mark j tWomey Mark J Twomey Enter your name (0=Exit): MARK TWOmeY Mark Twomey Enter your name (0=Exit): MARK Mark Enter your name (0=Exit): MR. MARK TWOMEY Esq. Mr. Mark Twomey Esq. Enter your name (0=Exit): 0 } -- Mark J. Twomey, | Motorola B.V. | m___ Swords, | @=" )~~ Pig Out ! County Dublin, | W W Ireland, | Europe. email at mjt@email.mot.com +----------------------------------