1

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*)

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Ganesh NB
  • 81
  • 1
  • 14
  • @Cyclone:- The link is for MYSQL and the question is tagged as Oracle! – Rahul Tripathi Oct 30 '15 at 10:05
  • 1
    I think you ned add brackets into your WHERE section (col1 like...) and (col2 ...) because the OR makes your logic bit different from what was expected – are Oct 30 '15 at 10:11
  • You can either put the lower() onto the COLs to simplify the logic, or use brackets to keep the ORs from messing up your AND – Michael Broughton Oct 30 '15 at 15:15

4 Answers4

3

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));
Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
0

Try this:

SELECT * from tab 
WHERE UPPER(col1) LIKE UPPER('b%')  
AND UPPER(col2) LIKE UPPER('b%');
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
0

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%');
Bala S
  • 495
  • 1
  • 6
  • 17
  • it's not... try to add insert into tabb values ('Kumar', 'bala',6); the problem is in missing brackets... should be (col1 like ) and (col2 like) – are Oct 30 '15 at 10:32
0

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%'
;
peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34