Oracle SQL in HR Schema Write a query to display “How many characters are there in each employee’s last name before the first ‘e’ or ‘E’ appears?”

Question

Write a query to display “How many characters are there in each employee’s last name before the first ‘e’ or ‘E’ appears?”

Answer

That is pretty much what the built in function INSTR() does – well, technically, it finds the positions of the 'e' so what you want is one less than that number.

To be safe, you can write this as:

select instr(lower(last_name) || 'e', 'e') - 1

This concatenates the 'e' to the name to be sure it returns all characters when there is no 'e'.

Accepted Answer

That is pretty much what the built in function INSTR() does &#8211; well, technically, it finds the positions of the 'e' so what you want is one less than that number.To be safe, you can write this as:select instr(lower(last_name) || 'e', 'e') - 1This concatenates the 'e' to the name to be sure it returns all characters when there is no 'e'.

Advertisement

Answer