Question

Can any one tell me an oracle statement to retrive all columns from emp table whose name starts with 'a' and also third character as 'a'?

Answer 1

Do you mean the column name or the contents of the table?

Answer 2

If you mean the person's name then you would use something like:

select * from employees where name LIKE 'a_a%'

The underscore takes up one character and the % takes up all characters in that position, in this case all the remaining characters of the name.

If you actually need the column names then do this:

select column_name from user_tab_columns
where table_name = 'EMPLOYEES'
and column_name like 'E_P%'

You might need to include functions to change the case just to make sure you get all data. Something like:

WHERE UPPER(NAME) LIKE 'A_A%'