Oracle SQL Query Displaying Empty Results

Question

I am writing a simple query to display the names of columns in my table.

This is the SQL I used:

select column_name from cols where table_name='StudentInfo';

For some reason, this query results in an empty result table even though my StudentInfo table does indeed have columns.

Try select distinct table_name from cols and check if "StudentInfo" shines up that way. Probably table names are stored in capital letters or in any other slightly different way. — Stephan Lechner
– Stephan Lechner, Commented Mar 1, 2017 at 7:01
Boom. You just solved my problem. Any idea why oracle has such sharp case sensitivity? — Gary
– Gary, Commented Mar 1, 2017 at 7:05
will definitely give an upvote and a check mark for that one. Thank you! Really appreciate it — Gary
– Gary, Commented Mar 1, 2017 at 7:08

Community · Accepted Answer · 2017-05-23 12:32:02Z

2

Table names might be stored in capital letters such that a condition table_name='StudentInfo' fails. Note that Oracle (and most other RDBMS I know) compare strings in a case sensitive manner. For a case insensitive comparison, use UPPER (or LOWER) on both arguments. So the following query should work safely:

select column_name from cols where upper(table_name)=upper('StudentInfo')

There are other ways of turning string comparison into case insensitive, like altering session parameters NLS_COMP and NLS_SORT (cf., for example, https://stackoverflow.com/a/5391234/2630032 and upvote this reference if applicable). By using UPPER, however, you make your query independent of such settings.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Mar 1, 2017 at 7:05

Stephan Lechner

35.2k4 gold badges38 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kinchit Dalwani · Accepted Answer · 2017-03-01 07:27:11Z

1

You can go for this:

select column_name from cols where lower(table_name) like '%studentinfo%'

edited Mar 1, 2017 at 7:27

answered Mar 1, 2017 at 7:10

Kinchit Dalwani

3984 silver badges19 bronze badges

2 Comments

Kinchit Dalwani Over a year ago

@StephanLechner Yes! It would display all records containg the string 'studentinfo'. But if we only want records which has the required sign then % should be removed from the query.

Stephan Lechner Over a year ago

Note that like is also case sensitive (depending on settings). See stackoverflow.com/a/5391234/2630032

Collectives™ on Stack Overflow

Oracle SQL Query Displaying Empty Results

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related