CODEWITHSUNDEEP
selecttriggersoracle10gcollationcase-insensitive
sando.lakatos
sando.lakatos

Reputation: 97

Ignore case for all selects on cetain table

is there a way in Oracle 10g to set a case insensitive search and possibly also translate for all incoming selects over certain tables?

I have a select like this:

SELECT city.city_name as city,
    city.city_zip as zip,
    city.city_name || ' - ' || county.county_name as county
FROM T_ADDRESSFILLCITY city
JOIN T_ADDRESSFILLCOUNTY county ON county.countyid = city.countyid
WHERE LOWER(TRANSLATE(city.city_name, 'úüűůéěäáôöőóťčížďňšŕýľřĺąćęłńóśźż', 'uuuueeaaooootcizdnsrylrlacelnoszz')) LIKE LOWER(TRANSLATE({PARAM}, 'úüűůéěäáôöőóťčížďňšŕýľřĺąćęłńóśźż', 'uuuueeaaooootcizdnsrylrlacelnoszz'))

but I'd like to change it to this

SELECT city.city_name as city,
    city.city_zip as zip,
    city.city_name || ' - ' || county.county_name as county
FROM T_ADDRESSFILLCITY city
JOIN T_ADDRESSFILLCOUNTY county ON county.countyid = city.countyid
WHERE city.city_name LIKE {PARAM}

and have a database mechanism (collate?, trigger?) to do the ignore case and translate by default for all selects.

But this should only apply for specific tables, not the entire database.

I could not find anything. Thank you in advance for tips.

Upvotes: 0

Views: 88

Answers (1)

DazzaL
DazzaL

Reputation: 21973

you should try to set:

 alter session set nls_comp='LINGUISTIC';
 alter session set nls_sort='BINARY_AI';

and then run the second query. This should provide a accent case insensitive search on LIKE. Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm#CIHJBFAD

alternative is to use convert. e.g.:

where upper(convert(name, 'US7ASCII')) like upper(convert('%e%', 'US7ASCII'));

Upvotes: 1

Related Questions