From: Dennis Pimple Subject: Non-case sensitive queries Date: Mon, 18 Apr 94 21:39:02 PDT Here it is. It's a brutal approach, and I wrote it a couple years ago, but it does the job. Dennis Dennis J. Pimple Informix Software Inc Senior Consultant Denver Colorado USA dennisp@informix.com Voice:303-850-0210 Fax:303-779-4025 ########### # INFORMIX PROFESSIONAL SERVICES ####### # ## Denver, Colorado ###### # ### ================================================== ##### # #### File: %M% SCCS: %I% %P% #### # ##### Program: ### # ###### Client: ## # ####### Author: Dennis Pimple # ########### Date: %G% %U% GLOBALS DEFINE wclause CHAR(1000) # where clause to format END GLOBALS #--------------------------------------------------------------------- FUNCTION frmt_clause() # Arguments: N/A, use global wclause so we don't pass a huge string # Purpose: Parse a where clause taken from a construct statement and # modify it to ignore case on any character string input. # The where clause needs to be quite large to take into # account all the stuff this function adds. eg: # matches "*hi mom*" becomes matches "*[Hh][Ii] [Mm][Oo][Mm]*" # ="unix" becomes matches "[Uu][Nn][Ii][Xx]" # Returns: N/A The modified where clause is GLOBALS because # we can't return text > 512 characters #--------------------------------------------------------------------- DEFINE nclause CHAR(1000) # formatted where clause DEFINE l SMALLINT # length of wclause DEFINE x SMALLINT # counter along string DEFINE y SMALLINT # another counter along string DEFINE sp SMALLINT # # of spaces to add DEFINE c CHAR(1) # c = wclause[x] DEFINE mt CHAR(7) # to see if we found the word "matches" DEFINE dt SMALLINT # TRUE if a date string suspected DEFINE brkt SMALLINT # TRUE if an open bracket "[" is current DEFINE txt CHAR(100) # to hold space brackets INITIALIZE nclause TO NULL INITIALIZE mt TO NULL LET sp = 0 LET l = LENGTH(wclause) FOR x = 1 TO l IF mt IS NOT NULL AND mt = "matches" THEN # we found the keyword matches # wclause[x] is currently pointing to the space after matches # put the space quote on the end of the clause LET nclause = nclause CLIPPED, " \"" LET x = x + 2 INITIALIZE mt TO NULL LET brkt = FALSE FOR x = x TO l IF wclause[x] = "\"" THEN # end of the matches string EXIT FOR END IF IF wclause[x] = "[" THEN # open bracket LET brkt = TRUE END IF IF wclause[x] = "]" THEN # close bracket LET brkt = FALSE END IF LET c = UPSHIFT(wclause[x]) IF c MATCHES "[A-Z]" AND NOT brkt THEN IF sp > 0 THEN LET txt = "[" FOR y = 1 TO sp LET txt = " ", txt CLIPPED END FOR LET nclause = nclause CLIPPED, txt CLIPPED LET sp = 0 ELSE LET nclause = nclause CLIPPED, "[" END IF LET nclause = nclause CLIPPED, UPSHIFT(c), DOWNSHIFT(c), "]" ELSE IF wclause[x] = " " THEN # we'll put the space in next time LET sp = sp + 1 ELSE IF sp > 0 THEN LET txt = wclause[x] FOR y = 1 TO sp LET txt = " ", txt CLIPPED END FOR LET nclause = nclause CLIPPED, txt CLIPPED ELSE LET nclause = nclause CLIPPED, wclause[x] END IF LET sp = 0 END IF END IF END FOR END IF WHILE wclause[x] = "=" AND wclause[x+1] = "\"" # need to check and make sure there's at lease one character # to change, so we know we're not mucking with a date field LET dt = TRUE FOR y = x+2 TO l IF wclause[y] = "\"" THEN # this is the end of the string EXIT FOR END IF LET c = UPSHIFT(wclause[y]) IF c MATCHES "[A-Z]" THEN LET dt = FALSE EXIT FOR END IF END FOR IF dt THEN # go on with regular processing; this is either a date # or a character sting with no alpha characters and # either way we have nothing to do EXIT WHILE END IF # If we got here, we've got a character string to work # wclause[x] is currently pointing to the = sign # put the matches space quote on the end of the clause LET nclause = nclause CLIPPED, " matches \"" LET x = x + 2 LET brkt = FALSE FOR x = x TO l IF wclause[x] = "\"" THEN # end of the string EXIT FOR END IF IF wclause[x] = "[" THEN # open bracket LET brkt = TRUE END IF IF wclause[x] = "]" THEN # close bracket LET brkt = FALSE END IF LET c = UPSHIFT(wclause[x]) IF c MATCHES "[A-Z]" AND NOT brkt THEN IF sp > 0 THEN LET txt = "[" FOR y = 1 TO sp LET txt = " ", txt CLIPPED END FOR LET nclause = nclause CLIPPED, txt CLIPPED LET sp = 0 ELSE LET nclause = nclause CLIPPED, "[" END IF LET nclause = nclause CLIPPED, UPSHIFT(c), DOWNSHIFT(c), "]" ELSE IF wclause[x] = " " THEN # we'll put the space in next time LET sp = sp + 1 ELSE IF sp > 0 THEN LET txt = wclause[x] FOR y = 1 TO sp LET txt = " ", txt CLIPPED END FOR LET nclause = nclause CLIPPED, txt CLIPPED ELSE LET nclause = nclause CLIPPED, wclause[x] END IF LET sp = 0 END IF END IF END FOR EXIT WHILE END WHILE # looking for the keyword "matches" CASE WHEN wclause[x] = "m" LET mt = "m" WHEN wclause[x] = "a" AND mt = "m" LET mt = "ma" WHEN wclause[x] = "t" AND mt = "ma" LET mt = "mat" WHEN wclause[x] = "c" AND mt = "mat" LET mt = "matc" WHEN wclause[x] = "h" AND mt = "matc" LET mt = "match" WHEN wclause[x] = "e" AND mt = "match" LET mt = "matche" WHEN wclause[x] = "s" AND mt = "matche" LET mt = "matches" IF wclause[x+1,x+2] = " \"" THEN # this is it ELSE # this is something else LET mt = "" END IF END CASE IF wclause[x] = " " THEN # we'll put the space in next time LET sp = sp + 1 ELSE IF sp > 0 THEN LET txt = wclause[x] FOR y = 1 TO sp LET txt = " ", txt CLIPPED END FOR LET nclause = nclause CLIPPED, txt CLIPPED ELSE LET nclause = nclause CLIPPED, wclause[x] END IF LET sp = 0 END IF END FOR LET wclause = nclause CLIPPED END FUNCTION # frmt_clause()