SHARE
TWEET

Untitled

a guest Jun 19th, 2019 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT employees.first_name, employees.last_name,
  2. replace(replace(first_name,'l',''),'i'),
  3. initcap(substr(rpad(employees.first_name,6,'_'),1,6)) || '-' ||
  4.  
  5. case when length(employees.last_name)>4
  6. then lower(substr(employees.last_name,-5,4))
  7. else lower(substr(lpad(employees.last_name,5,'_'),-5,4)) end ||
  8. upper(substr(employees.last_name,-1,1)) code
  9.  
  10. FROM employees
  11. ORDER BY last_name, first_name;
  12.      
  13. select first_name, last_name, f
  14.        ||'-'
  15.        ||substr(l, 1, length(l) - 1)
  16.        ||upper(substr(l, -1)) code
  17.   from (select first_name, last_name,
  18.                initcap(rpad(substr(translate(first_name, 'xil', 'x'), 1, 6), 6,
  19.                        '_')) f,
  20.                lpad(substr(translate(last_name, 'xil', 'x'),
  21.                            greatest(-6, -length(translate(last_name, 'xil', 'x')))), 6,
  22.                           '_')
  23.                           l
  24.           from employees);
  25.      
  26. SELECT ENAME,
  27.        JOB,
  28.           INITCAP (RPAD (REPLACE (REPLACE (ENAME, 'I'), 'i'), 6, '_'))
  29.        || '-'
  30.        || LPAD (
  31.              reverse (
  32.                 INITCAP (
  33.                    SUBSTR (reverse ( (REPLACE (REPLACE (JOB, 'I'), 'i'))),
  34.                            1,
  35.                            6))),
  36.              6,
  37.              '_')
  38.           code
  39.   FROM emp
  40.   ORDER BY JOB, Ename
  41.      
  42. SELECT e."First_Name", e."Last_Name",
  43.        initcap(rpad(replace(replace(e."First_Name", 'l'), 'i'),6,'_'))
  44.        || '-' ||
  45.        reverse(initcap(reverse(lpad(replace(replace(e."Last_Name", 'l'), 'i'),6,'_')))) "Code"
  46. FROM Employees e
  47. ORDER BY e."Last_Name", e."First_Name";
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top