CODEWITHSUNDEEP
oracle-database
Saiyok Bhattacherjee
Saiyok Bhattacherjee

Reputation: 11

Write a query to display the student id and address for the student "David". Note : The student name can be in any case

enter image description here

data in student table:

STUDENT_NAME STUDENT_ID   ADDRESS
------------ -----------  --------
  Anandhi          1        4th street
  Anitha           4        Cross cut
  david            7        Main Cross Street
  Kokila           8        Rao Street
  Mithali          9        OMR road

I have tried the query:

select student_id,address from student where student_name='david';

Expected Output:

STDENT_ID  ADDRESS
---------- ----------
         7 Main Cross Street

I have tried the following code, it executes successfully and gives the desired result, however it fails to clear one test case and IDK why?

Upvotes: 1

Views: 723

Answers (3)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Actual Oracle versions currently supported (12.1.0.2 is EOL already) have collate clause, so since Oracle 12.2 you can use collate clause on different levels: Column level, table level, schema level, database level, session level, query level:

https://oracle-base.com/articles/12c/column-level-collation-and-case-insensitive-database-12cr2

For example, for your case:

SELECT student_id,address FROM student WHERE student_name collate BINARY_AI = 'david';

Full test case with test data and results:

--test data:
with STUDENT(STUDENT_NAME,STUDENT_ID,ADDRESS) as 
(
select 'Anandhi' , 1, '4th street'        from dual union all
select 'Anitha'  , 4, 'Cross cut'         from dual union all
select 'david'   , 7, 'Main Cross Street' from dual union all
select 'Kokila'  , 8, 'Rao Street'        from dual union all
select 'Mithali' , 9, 'OMR road'          from dual 
) -- end of test data
-- main query:
SELECT student_id,address FROM student WHERE student_name collate BINARY_AI = 'david';

-- Results:
STUDENT_ID ADDRESS
---------- -----------------
         7 Main Cross Street

Upvotes: 0

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

Are you sure all student names are stored in LOWERCASE? Second, is it all rows in your question present in the table or is it a subset?

If the answer is NO and it is a subset then try

SELECT student_id,address FROM student WHERE LOWER(student_name) = 'david';

And if you are not sure about leading and trailing spaces then try

SELECT student_id,address FROM student WHERE TRIM(LOWER(student_name)) = 'david';

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

You may try lowercasing the student name column, then compare it to david in all lowercase.

SELECT ADDRESS
FROM student
WHERE LOWER(STUDENT_NAME) = 'david';

Upvotes: 1

Related Questions