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

enter image description here

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) enter image description here

In replace(replace(first_name,'l',''),'i') what are you actually replacing "i" with? — PM 77-1
– PM 77-1, Commented Apr 3, 2013 at 1:56
actually i want to replace it with null or "" but if i add replace character it is giving error about "parameter number is too many" — hakki
– hakki, Commented Apr 3, 2013 at 1:58
Then why didn't you? Why you just replaced "l" with empty string but not "i"? — PM 77-1
– PM 77-1, Commented Apr 3, 2013 at 2:00
because as far as i know replace function takes 2 parameters — hakki
– hakki, Commented Apr 3, 2013 at 2:01
Learning SQL is good. Learning troubleshooting is better. The first step to troubleshooting is 'divide and conquer'. Which in this case means reduce your expression to something much simpler, inspect it and then wrap it in the next level of expression, inspect it, and so on until you get the correct answer. So first of all check that replace(first_name,'l','') returns what you expect (by putting it into it's own column) and go from there. — Nick.Mc
– Nick.Mc, Commented Apr 3, 2013 at 2:24

DazzaL · Accepted Answer · 2013-04-03 02:28:30Z

3

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.

edited Apr 3, 2013 at 2:28

answered Apr 3, 2013 at 2:09

DazzaL

22.1k3 gold badges52 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hakki Over a year ago

But it is not giving expected output. For example column name is very complicated. Column names should like above picture :)

DazzaL Over a year ago

@hakiko the example was for the code part. see here to add the other two original columns: sqlfiddle.com/#!4/1fba7/1

hakki Over a year ago

this is nice solution but I already have datas, tables, columns. In your link you created it with your best. :) Question about existed datas. Oracle Express gives ready database for doing examples :)

hakki Over a year ago

whattt only you added "f" and all visuality has changed. Thank you:)

Martin Backasch · Accepted Answer · 2019-05-20 07:53:23Z

1

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

edited May 20, 2019 at 7:53

Martin Backasch

1,8993 gold badges24 silver badges34 bronze badges

answered Apr 3, 2013 at 10:18

Ankit Jaiswal

111 bronze badge

Comments

PM 77-1 · Accepted Answer · 2013-04-03 03:27:31Z

0

This code that somewhat follows your original logic:

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";

I'm using REVERSE twice so I can use INITCAP for the last name as well. I also omitted the 3rd parameter of REPLACE since the function uses empty string by default.

Here's a SQL Fiddle DEMO I build from the part of your data. Feel free to add more data.

answered Apr 3, 2013 at 3:27

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Collectives™ on Stack Overflow

Oracle PL/SQL String Formatting

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related