Oracle PL/SQL String Formatting

Question

I started learing Oracle PL/SQL and I downloaded Oracle Database 10g Express with same examples and questions.

There is a question which I could not solve.

Question is:

Write an SQL query to retrieve the first name, last name, and the code of each employee where the code of an employee is found as follows: Firstly remove all occurrences of the characters “i” and “l”, then, concatenate the first six letters of the name, a dash sign “-“, and the last six characters of the last name where only the first and last character of the code should be uppercase. If the name does not contain six letters, put underscores (“”) to the end of the piece; if the last name does not contain six letters, put underscores (“”) to the start of the piece. Order the list according to the last name, and then according to the name.

OUTPUT MUST BE LIKE THAT

I wrote something but it is totaly wrong and not clear. Which parts should I fix?

---

SELECT employees.first_name, employees.last_name,
replace(replace(first_name,'l',''),'i'),
initcap(substr(rpad(employees.first_name,6,'_'),1,6)) || '-' ||

case when length(employees.last_name)>4
then lower(substr(employees.last_name,-5,4))
else lower(substr(lpad(employees.last_name,5,'_'),-5,4)) end ||
upper(substr(employees.last_name,-1,1)) code

FROM employees
ORDER BY last_name, first_name;

This is my output(WRONG)

Answer 1

you can write it like this:

select first_name, last_name, f
       ||'-'
       ||substr(l, 1, length(l) - 1)
       ||upper(substr(l, -1)) code
  from (select first_name, last_name,
               initcap(rpad(substr(translate(first_name, 'xil', 'x'), 1, 6), 6,
                       '_')) f,
               lpad(substr(translate(last_name, 'xil', 'x'),
                           greatest(-6, -length(translate(last_name, 'xil', 'x')))), 6,
                          '_')
                          l
          from employees); 

i've assumed you only wanted to replace i and l and not also I and L. translate will act the same as replace(replace(str, 'l', ''), 'i', '') in this case.

Answer 2

This code exactly follows your requirement: Replace the column name and the table name with the desired values

SELECT ENAME,
       JOB,
          INITCAP (RPAD (REPLACE (REPLACE (ENAME, 'I'), 'i'), 6, '_'))
       || '-'
       || LPAD (
             reverse (
                INITCAP (
                   SUBSTR (reverse ( (REPLACE (REPLACE (JOB, 'I'), 'i'))),
                           1,
                           6))),
             6,
             '_')
          code
  FROM emp 
  ORDER BY JOB, Ename