Subject: Non-case sensitive queries
From: Dennis Pimple <[email-masked]>
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
[email-masked]     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()