Oracle SQL Functions

Concatenation ||

select city||country from location;

1    ATHENSGREECE
2    CHICAGOUNITED STATES
3    CONAKRYGUINEA
4    LIMAPERU
5    MADRASINDIA
6    MANCHESTERENGLAND
7    MOSCOWRUSSIA
8    PARISFRANCE
9    SHENYANGCHINA
10    ROMEITALY
11    TOKYOJAPAN
12    SYDNEYAUSTRALIA
13    SPARTAGREECE
14    MADRIDSPAIN


List city and country with comma with them
select city ||','||country from location;

1    ATHENS,GREECE
2    CHICAGO,UNITED STATES
3    CONAKRY,GUINEA
4    LIMA,PERU
5    MADRAS,INDIA
6    MANCHESTER,ENGLAND
7    MOSCOW,RUSSIA
8    PARIS,FRANCE
9    SHENYANG,CHINA
10    ROME,ITALY
11    TOKYO,JAPAN
12    SYDNEY,AUSTRALIA
13    SPARTA,GREECE
14    MADRID,SPAIN

RPAD and LPAD
padding . on the right side is better to guide your eyes

select rpad(city,35,'.'), temperature from weather;
1    LIMA...............................    45
2    PARIS..............................    81
3    MANCHESTER.........................    66
4    ATHENS.............................    97
5    CHICAGO............................    66
6    SYDNEY.............................    69
7    SPARTA.............................    74

adding characters on the left side of rows
select name, lpad(trim('"' from title),47,'*'), page from magazine
1    BERTRAND MONTHLY    **************THE BARBERS WHO SHAVE THEMSELVES.    70
2    LIVE FREE OR DIE    ***************HUNTING THOREAU IN NEW HAMPSHIRE    320
3    PSYCHOLOGICA    ************************THE ETHNIC NEIGHBORHOOD    246
4    FADED ISSUES    *****************RELATIONAL DESIGN AND ENTHALPY    279
5    ENTROPY WIT    ********************INTERCONTINENTAL RELATIONS.    20
 

TRIM
trim off double quotations 
 select title from magazine;
1    THE BARBERS WHO SHAVE THEMSELVES.
2    "HUNTING THOREAU IN NEW HAMPSHIRE"
3    THE ETHNIC NEIGHBORHOOD
4    RELATIONAL DESIGN AND ENTHALPY
5    "INTERCONTINENTAL RELATIONS."
 
select trim ('"' from title) from magazine;
1    THE BARBERS WHO SHAVE THEMSELVES.
2    HUNTING THOREAU IN NEW HAMPSHIRE
3    THE ETHNIC NEIGHBORHOOD
4    RELATIONAL DESIGN AND ENTHALPY
5    INTERCONTINENTAL RELATIONS.

LOWER, UPPER and INITCAP
 select city, upper(city), lower(city), initcap(lower(city)) from weather;
1    LIMA    LIMA    lima    Lima
2    PARIS    PARIS    paris    Paris
3    MANCHESTER    MANCHESTER    manchester    Manchester
4    ATHENS    ATHENS    athens    Athens
5    CHICAGO    CHICAGO    chicago    Chicago
6    SYDNEY    SYDNEY    sydney    Sydney
7    SPARTA    SPARTA    sparta    Sparta



Length
select name, length(name) from magazine;
1    BERTRAND MONTHLY    16
2    LIVE FREE OR DIE    16
3    PSYCHOLOGICA    12
4    FADED ISSUES    12
5    ENTROPY WIT    11





SUBSTR

5  and 8  mean how many characters from most left side exist

select lastname, firstname, substr(phone,5,8) from address where phone like '415-%'
1    ADAMS    JACK    555-7530
2    ZACK    JACK    555-6842
3    YARROW    MARY    555-2178
4    WERSCHKY    ARNY    555-7387
5    BRANT    GLEN    555-7512
6    EDGAR    THEODORE    555-6252

you also can use negative number to show characters for position value, it's relative to the end of the string. for example,

select rpad (lastname||','||firstname,25,'.') as name, substr(phone,-8) as phone
from address where phone like '415-%'
1    ADAMS,JACK...............    555-7530
2    ZACK,JACK................    555-6842
3    YARROW,MARY..............    555-2178
4    WERSCHKY,ARNY............    555-7387
5    BRANT,GLEN...............    555-7512
6    EDGAR,THEODORE...........    555-6252
use this feature only for VARCHAR2 datatype columns, Do not use it with columns that use the CHAR datatyp. CHAR columns are fixed-length columns, so their values are padded with spaces to extend them to the full length of the column. 

INSTR
it simply tells you where in the string it found what you were searching for.
select author, INSTR(author,'O') from magazine;
1    BONHOEFFER, DIETRICH    2
2    CHESTERTON, G.K.    9
3    RUTH, GEORGE HERMAN    9
4    WHITEHEAD, ALFRED    0
5    CROOKES, WILLIAM    3


SUBSTR
changing the first name and last name using instr and substr fuctions.

select author, substr(author,instr(author,',')+2)
               ||' '||
               substr(author,1,instr(author,',')-1)
               as ByFirstName
               from magazine

1    BONHOEFFER, DIETRICH    DIETRICH BONHOEFFER
2    CHESTERTON, G.K.    G.K. CHESTERTON
3    RUTH, GEORGE HERMAN    GEORGE HERMAN RUTH
4    WHITEHEAD, ALFRED    ALFRED WHITEHEAD
5    CROOKES, WILLIAM    WILLIAM CROOKES

ASCII and CHR
CHR convertsf numeric values to their ASCII character string equivalents
 select CHR(70)||CHR(83)||chr(79)||chr(85)||chr(71)
       AS CharValues
         from DUAL;
1    FSOUG


DUMP
to see each ASCII value, you can use the DUMP function
select DUMP('FSOUG') from DUAL;
1    Typ=96 Len=5: 70,83,79,85,71


SOUNDEX
It is used almost exclusively in a where clause. It has the unusual ability to find words that sound like other words, virtually regardless of how either is spelled.

 select author from magazine
where soundex (author) = soundex('banheffer');
1    BONHOEFFER, DIETRICH



one useful purpose for this function is cleaning up mailing lists.
 select a.lastname, a.firstname, a.phone
from address a, address b
where soundex (a.lastname) = soundex(b.lastname);