sql - Oracle PL/SQL 字符串格式化

Question

我开始学习 Oracle PL/SQL，并下载了带有相同示例和问题的 Oracle Database 10g Express。

有一个问题我无法解决。

问题是:

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

FROM employees
ORDER BY last_name, first_name;

这是我的输出(错误)

Answer 1

你可以这样写:

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 和 l 而不是 I 和 L。在这种情况下，翻译将与 replace(replace(str, 'l', ''), 'i', '') 相同。