CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Ganesh NB
Ganesh NB

Reputation: 81

Case sensitive sql search

I need to retrive data with out case sensitive in sql.

SELECT * from tab 
WHERE col1 LIKE LOWER('b%') OR col1 LIKE UPPER('b%') 
AND col2 LIKE LOWER('b%') OR col2 LIKE UPPER('b%');

this one retrive all the row in the table. but i need only the rows with col1 (B* or b*) and col2 with(B* or b*)

Upvotes: 1

Views: 63

Answers (4)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

Another alternative way:

alter session set nls_comp = linguistic;
alter session set nls_sort = binary_ci;

SELECT *
FROM tab 
WHERE col1 LIKE 'b%'
    AND col2 LIKE 'b%'
;

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220877

This would be the query you wanted to write:

SELECT * 
FROM tab 
WHERE (col1 LIKE LOWER('b%') OR col1 LIKE UPPER('b%'))
AND (col2 LIKE LOWER('b%') OR col2 LIKE UPPER('b%'))

The parentheses are required because of operator precedence.

Of course, you could also write this instead:

SELECT * 
FROM tab 
WHERE LOWER(col1) LIKE 'b%'
AND LOWER(col2) LIKE 'b%'

... and possibly, put a function-based indexes on

CREATE INDEX idx1 ON tab(LOWER(col1));
CREATE INDEX idx2 ON tab(LOWER(col2));

Upvotes: 3

Bala S
Bala S

Reputation: 523

Your code also working fine only. What you're expecting?

create table tabb (col1 varchar2(20), col2 varchar2(20), test number);

begin
insert into tabb values ('Bala', 'bala',1);
insert into tabb values ('bala', 'Bala',2);
insert into tabb values ('Baba', 'baba',3);
insert into tabb values ('bala', 'Bala',4);
insert into tabb values ('Kumar', 'kumar',5);
end;

select * from tabb where col1 like lower('b%') or col1 like upper('b%') and col2 like lower('b%') or col2 like upper('b%');

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

Try this:

SELECT * from tab 
WHERE UPPER(col1) LIKE UPPER('b%')  
AND UPPER(col2) LIKE UPPER('b%');

Upvotes: 0

Related Questions