The most important Oracle text functions are listed in the following table.
| Function | Description |
|---|---|
| LENGTH(t) | Length (expressed in characters) of t |
| ASCII(t) | ASCII value of first character of t |
| CHR(n) | Character with ASCII value n |
| UPPER(t), LOWER(t) | t in uppercase/lowercase |
| INITCAP(t) | Each word in t with initial uppercase; remainder in lowercase |
| LTRIM(t[,k]) | Remove characters from the left of t, until the first character not in k |
| RTRIM(t[,k]) | Remove characters from the right of t, after the last character not in k |
| TRIM([[option][c FROM]]t) | Trim character c from t; option = LEADING, TRAILING, or BOTH |
| LPAD(t,n[,k]) | Left-pad t with sequence of characters in k to length n |
| RPAD(t,n[,k]) | Right-pad t with k to length n (the default k is a space) |
| SUBSTR(t,n[,m]) | Substring of t from position n, m characters long (the default for m is until end) |
| INSTR(t,k) | Position of the first occurrence of k in t |
| INSTR(t,k,n) | Same as INSTR(t,k), but starting from position n in t |
| INSTR(t,k,n,m) | Same as INSTR(t,k,n), but now the mth occurrence of k |
| TRANSLATE(t,v,w) | Replace characters from v (occurring in t) by corresponding character in w |
| REPLACE(t,v) | Remove each occurrence of v from t |
| REPLACE(t,v,w) | Replace each occurrence of v in t by w |
| CONCAT(t1,t2) | Concatenate t1 and t2 (equivalent to the || operator) |
When counting positions in strings, always start with one, not with zero.
Several text functions have a corresponding function with a B suffix, such as SUBSTRB, INSTRB, and LENGTHB.
These special functions express their results in bytes instead of characters.
This distinction is relevant only if you are using multibyte character sets.
The following code shows some examples of the LOWER, UPPER, INITCAP, and LENGTH text functions.
SQL> SQL> drop table emp; Table dropped.-- ww w .j a v a 2 s . c om SQL> create table emp( 2 empno NUMBER(4) primary key, 3 ename VARCHAR2(8) not null , 4 init VARCHAR2(5) not null , 5 job VARCHAR2(8) , 6 mgr NUMBER(4) , 7 bdate DATE not null , 8 msal NUMBER(6,2) not null , 9 comm NUMBER(6,2) , 10 deptno NUMBER(2) default 10) ; SQL> SQL> insert into emp values(7001,'SMITH','N', 'TRAINER', 7902,date '1975-12-17', 1800 , NULL, 20); SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20', 1600, 300, 30); SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02', 1250, 500, 10); SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02', 2975, NULL, 20); SQL> insert into emp values(7005,'BROWN','P', 'SALESREP',7006,date '1976-09-28', 1250, 1400, 30); SQL> insert into emp values(7006,'BLAKE','R', 'MANAGER', 7009,date '1973-11-01', 2850, NULL, 10); SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09', 2450, NULL, 10); SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26', 3000, NULL, 20); SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17', 5000, NULL, 10); SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28', 1500, 0, 30); SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30', 1100, NULL, 20); SQL> insert into emp values(7012,'JONES','R', 'ADMIN', 7006,date '1979-10-03', 8000, NULL, 30); SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13', 3000, NULL, 20); SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN', 7007,date '1972-01-23', 1300, NULL, 10); SQL> SQL> SQL> select lower(job), initcap(ename) 2 from emp 3 where upper(job) = 'SALESREP' 4 order by length(ename); LOWER(JO | INITCAP( -------- | -------- salesrep | Ward salesrep | Bread salesrep | Brown salesrep | Allen SQL>