Re: 9.14 Non-Case-Sensitive comparision of varchars?
Michael Koch (kochm@informatik.tu-muenchen.de) wrote:
: After checking all the documentation I dare to ask this:
: Does anybody know how one can compare a VARCHAR field to a
: constant string in a non-case-sensitive way in a select
: statment with Dynamic Server 9.14?
/*
*
* File: case.c
*
* This file contains a couple of functions to manage conversions of
* character strings to UPPER or lower case.
*
* 1. Compile this stuff into a shared library. Check out the
* DBDK product to get it to generate the Makefiles for
* various platforms.
*
* 2. Put the shared library someplace the engine can get to it.
* Note how, in the example that follows, the file is
* to be found in the $INFORMIXDIR/extend/Examples/bin dir.
*
* 3. Run the following SQL through the database. I've deliberately
* named the functions to as to avoid naming conflicts with the
* similar stuff in the 9.2 server.
*
* CREATE FUNCTION ToUpper ( LVARCHAR )
* RETURNS LVARCHAR
* WITH ( NOT VARIANT, PARALLELIZABLE )
* EXTERNAL NAME '$INFORMIXDIR/extend/Examples/bin/Case.bld(to_upper)'
* LANGUAGE C;
* --
* GRANT EXECUTE ON FUNCTION ToUpper( VARCHAR(128) ) TO PUBLIC;
* --
* CREATE FUNCTION ToLower ( LVARCHAR )
* RETURNS LVARCHAR
* WITH ( NOT VARIANT, PARALLELIZABLE )
* EXTERNAL NAME '$INFORMIXDIR/extend/Examples/bin/Case.bld(to_lower)'
* LANGUAGE C;
* --
* GRANT EXECUTE ON FUNCTION ToLower( LVARCHAR ) TO PUBLIC;
*
* 4. The thing works in the following way:
*
--
-- File: CaseTest.sql
--
-- This script is here to test and to demonstrate the use of
-- the Case and Soundex functions.
--
-- DROP TABLE Surnames;
-- DROP TABLE FirstNames;
-- DROP TABLE People;
-- DROP ROW TYPE Name_Type RESTRICT;
-- DROP FUNCTION DNow();
--
CREATE ROW TYPE Name_Type
( Id integer,
Name varchar(48)
);
--
CREATE TABLE SurnamesOF TYPE Name_Type;
CREATE TABLE FirstNamesOF TYPE Name_Type;
--
LOAD FROM 'Surnames'DELIMITER ' '
INSERT INTO Surnames;--
LOAD FROM 'FirstNames'DELIMITER ' '
INSERT INTO FirstNames;--
--
CREATE TABLE People (
Id INTEGER NOT NULL,
FirstName VARCHAR(48) NOT NULL,
Surname VARCHAR(48) NOT NULL
);--
-- NOTE: The last value for his predicate indicates the percentage
-- of the rows from either table to include in the mix. In
-- this case, at 50 x 10 you get about 18000 rows. Using
-- this technique you can get to (512 x 742) rows.
--
INSERT INTO People
SELECT F.Id * 800 + S.Id,
F.Name,
S.Name
FROM FirstNames F, Surnames S
WHERE MOD((S.Id*10023), 101) < 50
AND MOD((F.Id*10023), 101) < 10;--
-- Now. These are the queries to check whether the index is being used.
--
CREATE FUNCTION DNow()
RETURNING DateTime DAY TO FRACTION(3)
RETURN CURRENT;END FUNCTION;
--
EXECUTE FUNCTION DNow();--
SELECT COUNT(*)
FROM People P
WHERE ToUpper(P.Surname) = ToUpper('Brown');--
EXECUTE FUNCTION DNow();--
-- NB: This bit won't work by default. Look at the note
-- at the bottom of the comments.
--
CREATE INDEX P_Ndx1 ON People(ToUpper(Surname));--
UPDATE STATISTICS HIGH;--
EXECUTE FUNCTION DNow();--
SELECT COUNT(*)
FROM People P
WHERE ToUpper(P.Surname) = ToUpper('Brown');--
EXECUTE FUNCTION DNow();--
*
* NOTE: There is a gotcha. If you have a field > 128 bytes, then you
* get an error on the index build. If you want to use a functional
* index with this stuff you'll need to define the functions
* a little differently -- VARCHAR(128) argument and return value, not
* lvarchar -- and you ought to put a length check in each function.
* I've included this check in the code below. If you want to index
* it, then compile with the -DCHECK_OK_FOR_INDEX flag.
*
*/
#include <ctype.h>
#include <stdio.h>
#include <string.h>
#include <stddef.h>
#include <mi.h>
#define INDEX_MAX_LEN 128
mi_lvarchar *
to_upper(intext)
mi_lvarchar * intext;
{
mi_string * pString,
* pCh;
#ifdef CHECK_OK_FOR_INDEX
if ( mi_get_varlen(intext) > INDEX_MAX_LEN )
mi_db_error_raise( (MI_CONNECTION *) NULL, MI_EXCEPTION, "ToUpper: Too long for Index.");
#endif
pCh = (pString = mi_lvarchar_to_string(intext));
while(*pCh) {
*pCh = (char)toupper((int)*pCh);
pCh++;
}
return mi_string_to_lvarchar(pString);
}
mi_lvarchar *
to_lower(intext)
mi_lvarchar * intext;
{
mi_string * pString,
* pCh;
#ifdef CHECK_OK_FOR_INDEX
if ( mi_get_varlen(intext) > INDEX_MAX_LEN )
mi_db_error_raise( (MI_CONNECTION *) NULL, MI_EXCEPTION, "ToLower: Too long for Index.");
#endif
pCh = (pString = mi_lvarchar_to_string(intext));
while(*pCh) {
*pCh = (char)tolower((int)*pCh);
pCh++;
}
return mi_string_to_lvarchar(pString);
}