0

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.

Gary
  • 67
  • 1
  • 8

2 Answers2

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.

Community
  • 1
  • 1
Stephan Lechner
  • 34,891
  • 4
  • 35
  • 58
1

You can go for this:

select column_name from cols where lower(table_name) like '%studentinfo%'
Kinchit Dalwani
  • 398
  • 4
  • 19
  • @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. – Kinchit Dalwani Mar 01 '17 at 07:19
  • 1
    Note that `like` is also case sensitive (depending on settings). See http://stackoverflow.com/a/5391234/2630032 – Stephan Lechner Mar 01 '17 at 07:24