Highlighted

Oracle 11g case-insensitive searching

LEGEND ,
Apr 19, 2018

Copy link to clipboard

Copied

Hello, all,

I am using Oracle 11g, do not have admin access or authority to make any changes to configuration, and would like to have case-insensitive searches of varchar2 and CLOB columns.

I have tried putting the following before my SELECT query:

ALTER SESSION SET NLS_SORT = BINARY_CI;

ALTER SESSION SET NLS_COMP = LINGUISTIC;

This results in an error message: ORA-00922; missing or invalid option.

Also, the method I'm using for searching the CLOB is case-sensitive:

SELECT * /* I don't use * in production, I'm using it here for brevity */

FROM table1

WHERE dbms_lob.instr(columnA,'searchWord') >= 1

Any advice appreciated.

V/r,

^ _ ^

TOPICS
Database access

Views

743

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Oracle 11g case-insensitive searching

LEGEND ,
Apr 19, 2018

Copy link to clipboard

Copied

Hello, all,

I am using Oracle 11g, do not have admin access or authority to make any changes to configuration, and would like to have case-insensitive searches of varchar2 and CLOB columns.

I have tried putting the following before my SELECT query:

ALTER SESSION SET NLS_SORT = BINARY_CI;

ALTER SESSION SET NLS_COMP = LINGUISTIC;

This results in an error message: ORA-00922; missing or invalid option.

Also, the method I'm using for searching the CLOB is case-sensitive:

SELECT * /* I don't use * in production, I'm using it here for brevity */

FROM table1

WHERE dbms_lob.instr(columnA,'searchWord') >= 1

Any advice appreciated.

V/r,

^ _ ^

TOPICS
Database access

Views

744

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 19, 2018 0
Advocate ,
Apr 24, 2018

Copy link to clipboard

Copied

I'm not familiar with Oracle, but see if you can do something similar to the following:

where LCase(columnA) like '%searchword%'

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 24, 2018 0
LEGEND ,
Apr 24, 2018

Copy link to clipboard

Copied

This will work, but at a huge cost.  Forcing Oracle to convert all text to lowercase is CPU intensive.  And, I'm not sure that would work with CLOB columns.

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 24, 2018 0
LEGEND ,
Apr 30, 2018

Copy link to clipboard

Copied

Been doing more Google searching on this subject, and I'm not finding much.  A lot of sites indicate the solution that gives me the "mising or invalid option" error message is the best, but it's not working, for me.

Any other suggestions?

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 30, 2018 0