Oracle SQL - Select Text Functions


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> 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> 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> select lower(job), initcap(ename)
  2  from   emp
  3  where  upper(job) = 'SALESREP'
  4  order  by length(ename);

-------- | --------
salesrep | Ward
salesrep | Bread
salesrep | Brown
salesrep | Allen


Related Topics