Geek asks and answers

Formatting strings in PL / SQL format

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

There is a question that I could not solve.

Question:

Write an SQL query to retrieve the name, surname and code of each employee where the employee code is found: First, delete all occurrences of the characters "i" and "l", then combine the first six letters of the name, the "-" sign, and the last six characters of the surname , where only the first and last Character of the code should be uppercase. If the name does not contain six letters, put underscores (") at the end of the fragment; if the last name does not contain six letters, put underscores (" ") at the beginning of the work. Order the list according to the last name and then according to the name.

EXIT SHOULD BE SO THAT

enter image description here

I wrote something, but it’s not so clear. What 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 conclusion (WRONG) enter image description here

+5
source share
3 answers

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 assumed that you wanted to replace iboth l, and also not iand l. translate will act in the same way as replace(replace(str, 'l', ''), 'i', '')in this case.

+3
source

This code exactly matches your requirements: replace the column name and 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
+1
source

, :

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 . .

0
source

More articles:


All Articles