QBE that returns both e and

[Geoff Dunmore]

Is there a way to allow a user to enter a value in a QBE field such as 'Noel' and have it return 'Noel' and 'No

 

[Marco Kalter]

I played with the nls_sort and nls_comp settings, but it seems like Oracle will never see 'noel' and 'no

 

[Stefan]

Don't think DAO can handle it locally - will have to be implemented server side.

3 ways i can think of:

a) Setup and use an Oracle Context index on this field.

b) create a function based index with your own text normalization function to get rid of any special characters (lookup the "DECOMPOSE" function in Oracle9i!)

c) for example, your table has a field "CLIENTNAME" then create another field called "CLIENTNAMESEARCH". Write a database trigger that keeps this search column synchronized with the value in clientname field, but of course in normalized characters. Now when you search for names use this clientnamesearch filed instead of clientname directly.

Cheers

 

[al0]

Originally posted by Stefan:
Don't think DAO can handle it locally - will have to be implemented server side.

3 ways i can think of:

a) Setup and use an Oracle Context index on this field.

b) create a function based index with your own text normalization function to get rid of any special characters (lookup the "DECOMPOSE" function in Oracle9i!)

c) for example, your table has a field "CLIENTNAME" then create another field called "CLIENTNAMESEARCH". Write a database trigger that keeps this search column synchronized with the value in clientname field, but of course in normalized characters. Now when you search for names use this clientnamesearch filed instead of clientname directly.

Cheers

Not sure about (a) - never have worked with context indexes.

(b)as it is presented will not work in QBE case (and original question was about QBE). Oracle optimizer can use function-based index only if query contains in the predicate that function on which index is built (i.e. to use FINDEX built on mytable(myfunc(myfield)) query shall contain 'mumu'=myfunc(myfield) and I see no way to ensure this in QBE mode. The workaround is to create over the table a view that contains one additional field - myfunc(myfield) myfuncfield
and use this view instead of table.

(c) is not better then (b) and even slightly worse (this approach would have more impact on DML operation performance and would not work if table data will be loaded with direct load (triggers will not fire on direct load).

And both (b) and (c) there is one drawback - real data (for "Noel" and "N

 

[Geoff Dunmore]

Hello all:

Thank-you for your kind replies to my question. you have presented me with several options which I will have to try out.

On the surface, the Context Index seems like overkill and may incur more overhead (meant to index content of whole documents) than is needed for this search.

The decompose function is intriguing and may work in conjunction with a view, but again would incur overhead and is a bit cludgy.

The problem being is that I am operating against Oracle 8

After more trial and error, I found this query structure would work

SQL> select lname from employeedata where SOUNDEX(lname) = SOUNDEX('Noel');

LNAME
------------------------------
Noel
No