Oracle PL / SQL字符串格式

有一个问题我无法解决.

问题是：

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.

输出必须是这样的

我写了一些东西,但这完全错了,不清楚.我应该修复哪些部分？

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,4)) end ||
upper(substr(employees.last_name,-1,1)) code

FROM employees
ORDER BY last_name,first_name;

这是我的输出(错误)

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

我假设你只想替换i和l而不是我和L.翻译的行为与替换相同(替换(str,’l’,”),’i’,”)在这种情况下.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!