Oracle PL/SQL字符串格式化

Question

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

其中一个问题我无法解决。

问题是：

编写SQL查询以检索每个员工的名字、姓氏和代码，其中找到员工的代码如下所示：

首先删除所有字符“i”和“l”的出现，然后将姓名的前六个字母、破折号“-”和姓氏的最后六个字符连接在一起，其中代码的第一个和最后一个字符应该是大写的。如果名字不包含六个字母，则在段尾放置下划线（“”）；如果姓氏不包含六个字母，则在段首放置下划线（“”）。按照姓和名的顺序对列表进行排序。 输出必须如下所示： 我写了一些东西，但它完全错误且不清晰。我应该修改哪些部分？

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;

这是我的输出（错误）

- hakkikonu

---

在 replace(replace(first_name,'l',''),'i') 中，你实际上是用什么替换 "i"？ - PM 77-1

实际上我想将它替换为null 或 ""，但如果我添加替换字符，它会出现“参数过多”的错误提示。 - hakkikonu

那为什么你不这样做呢？为什么你只是用一个空字符串替换了“l”，而没有替换“i”呢？ - PM 77-1

请查看 http://www.techonthenet.com/oracle/functions/replace.php。 - PM 77-1

学习SQL很好，但学习故障排除更好。故障排除的第一步是“分而治之”。在这种情况下，意味着将表达式简化为更简单的形式，检查它，然后将其包装在下一个级别的表达式中，再次检查它，以此类推，直到获得正确的答案。因此，首先检查replace(first_name,'l','')是否返回您期望的结果（通过将其放入自己的列中），然后从那里开始。 - Nick.McDermaid

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。在这种情况下，translate 的作用与 replace(replace(str, 'l', ''), 'i', '') 相同。

- DazzaL

---

但它没有给出预期的输出。例如，列名非常复杂。列名应该像上面的图片 :) - hakkikonu

@hakiko 这个例子是针对代码部分的。请参见此处以添加另外两列原始数据：http://sqlfiddle.com/#!4/1fba7/1 - DazzaL

这是一个不错的解决方案，但我已经有了数据、表格和列。在你的链接中，你用最好的方式创建了它。:) 关于现有数据的问题。Oracle Express提供了一个准备好的数据库来进行示例 :) - hakkikonu

你只是添加了一个"f"，但视觉效果已经完全改变了。谢谢:) - hakkikonu

Answer 2

这段代码完全满足您的要求： 用所需的值替换列名和表名。

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

- Ankit Jaiswal

Answer 3

这段代码在某种程度上遵循了您的原始逻辑：

SELECT e."First_Name", e."Last_Name",
       initcap(rpad(replace(replace(e."First_Name", 'l'), 'i'),6,'_'))
       || '-' ||
       reverse(initcap(reverse(lpad(replace(replace(e."Last_Name", 'l'), 'i'),6,'_')))) "Code"
FROM Employees e
ORDER BY e."Last_Name", e."First_Name";

我使用两次REVERSE，这样我就可以对姓氏也使用INITCAP。我还省略了REPLACE的第三个参数，因为该函数默认使用空字符串。

这是一个我从你的数据部分构建的SQL Fiddle DEMO。请随意添加更多数据。

- PM 77-1