Mega Code Archive
Trim leading and ending letters
<![CDATA[
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL> insert into employees values(1,'Jason', 'N', 'TRAINER', 2, date '1965-12-18', 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,'Jerry', 'J', 'SALESREP',3, date '1966-11-19', 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,'Jord', 'T' , 'SALESREP',4, date '1967-10-21', 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,'Mary', 'J', 'MANAGER', 5, date '1968-09-22', 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,'Joe', 'P', 'SALESREP',6, date '1969-08-23', 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,'Black', 'R', 'MANAGER', 7, date '1970-07-24', 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,'Red', 'A', 'MANAGER', 8, date '1971-06-25', 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,'White', 'S', 'TRAINER', 9, date '1972-05-26', 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,'Yellow', 'C', 'DIRECTOR',10, date '1973-04-27', 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,'Pink', 'J', 'SALESREP',null,date '1974-03-28', 2400, 0, 30);
1 row created.
SQL>
SQL> select ename
2 , ltrim(ename,'S') as ltrim_s
3 , rtrim(ename,'S') as rtrim_s
4 from employees;
ENAME LTRIM_S RTRIM_S
-------- -------- --------
Jason Jason Jason
Jerry Jerry Jerry
Jord Jord Jord
Mary Mary Mary
Joe Joe Joe
Black Black Black
Red Red Red
White White White
Yellow Yellow Yellow
Pink Pink Pink
10 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
]]>