Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
- 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);
- 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
- 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";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement