Oracle String Functions
 
    Version 10.1

ASCII
Get The ASCII Value Of A Character     ASCII(<string_or_column>)
SELECT ASCII('A') FROM dual;
SELECT ASCII('Z') FROM dual;
SELECT ASCII('a') FROM dual;
SELECT ASCII('z') FROM dual;
SELECT ASCII(' ') FROM dual;
 
ASCIISTR
Converts An ASCII String To An ASCII String In The Database's Character Set     ASCIISTR(<string_or_column>)
SELECT ASCIISTR(CHR(128) || 'Hello' || CHR(255))
FROM dual;
 
CASE Related Functions
Upper Case     UPPER(<string_or_column>)
SELECT UPPER('Dan Morgan') FROM dual;
Lower Case     LOWER(<string_or_column>)
SELECT LOWER('Dan Morgan') FROM dual;
Initial Letter Upper Case     INITCAP(<string_or_column>)
SELECT INITCAP('DAN MORGAN') FROM dual;
NLS Upper Case     NLS_UPPER(<string_or_column>)
SELECT NLS_UPPER('Dan Morgan', 'NLS_SORT = XDanish')
FROM dual;
NLS Lower Case     NLS_LOWER(<string_or_column>)
SELECT NLS_LOWER('Dan Morgan', 'NLS_SORT = XFrench')
FROM dual;
NLS Initial Letter Upper Case     NLS_INITCAP(<string_or_column>)
SELECT NLS_INITCAP('DAN MORGAN', 'NLS_SORT = XGerman')
FROM dual;
 
CAST
      CAST(<string_or_column> AS <DATATYPE>)
SELECT CAST(15402 AS VARCHAR2(30))
FROM dual;
 
CHR
Character     CHR(<ascii_string_or_column>>)
SELECT(CHR(68) || CHR(65) || CHR(78)) FROM dual;

SELECT(CHR(68) || CHR(97) || CHR(110)) FROM dual;
 
COALESCE
Returns the first non-null occurrence     COALESCE(<value>, <value>, <value>, ...)
CREATE TABLE test (
col1  VARCHAR2(1),
col2  VARCHAR2(1),
col3  VARCHAR2(1));

INSERT INTO test VALUES (NULL, 'B', 'C');
INSERT INTO test VALUES ('A', NULL, 'C');
INSERT INTO test VALUES (NULL, NULL, 'C');
INSERT INTO test VALUES ('A', 'B', 'C');

SELECT COALESCE(col1, col2, col3) FROM test;
 
COMPOSE
Convert a string in any data type to a Unicode string     COMPOSE(<string_or_column>)
Unistring Value     Resulting character
unistr('\0300')     grave accent (`)
unistr('\0301')     acute accent (´)
unistr('\0302')     circumflex (ˆ)
unistr('\0303')     tilde (~)
unistr('\0308')     umlaut (¨)
SELECT 'ol' || COMPOSE('e' || UNISTR('\0301')) FROM dual;
 
CONCAT
Concatenate     CONCAT(<first_string_or_column>>, <second_string_or_column>>)
SELECT CONCAT('Dan ', 'Morgan') FROM dual;
 
CONVERT
Converts From One Character Set To Another     CONVERT(<character>,<destination_character_set>,
<source_character_set>)
SELECT CONVERT('Ä Ê Í Õ Ø A B C D E','US7ASCII','WE8ISO8859P1')
FROM dual;
 
DECOMPOSE
Converts a unicode string to a string     DECOMPOSE(<unicode_string>)
SELECT DECOMPOSE('ol' || COMPOSE('e' || UNISTR('\0301')))
FROM dual;
 
INSTR
See links at page bottom
 
LENGTH
String Length     LENGTH(<string_or_column>)
SELECT LENGTH('Dan Morgan') FROM dual;
 
LPAD
Left Pad     LPAD(<string_or_column>, <final_length>, <padding_character>)
SELECT LPAD('Dan Morgan', 25, 'x') FROM dual;
 
LTRIM
Left Trim     LTRIM(<string_or_column>)
SELECT LTRIM('   Dan Morgan   ') FROM dual;
 
NLSSORT
returns the string of bytes used to sort a string.

The string returned is of RAW data type     NLSSORT(<column_name>, 'NLS_SORT = <NLS Parameter>);
CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('Gâberd');
COMMIT;

SELECT * FROM test ORDER BY name;

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');
 
REPLACE
See links at page bottom
 
REVERSE
Reverse     REVERSE(<string_or_column>)
SELECT REVERSE('Dan Morgan') FROM dual;

SELECT DUMP('Dan Morgan') FROM dual;
SELECT DUMP(REVERSE('Dan Morgan')) FROM dual;
 
RPAD
Right Pad     RPAD(<string_or_column>, <final_length>, <padding_character>)
SELECT RPAD('Dan Morgan', 25, 'x') FROM dual;
 
RTRIM
Right Trim     RTRIM(<string_or_column>)
SELECT RTRIM('   Dan Morgan   ') FROM dual;
 
SOUNDEX
Returns Character String Constaining The Phonetic Representation Of Another String     Rules:

    * Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y
    * Assign numbers to the remaining letters (after the first) as
      follows:
      b, f, p, v = 1
      c, g, j, k, q, s, x, z = 2
      d, t = 3
      l = 4
      m, n = 5
      r = 6
    * If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.
    * Return the first four bytes padded with 0.

SOUNDEX(<string_or_column>)
CREATE TABLE test (
name VARCHAR2(15));

INSERT INTO test VALUES ('Smith');
INSERT INTO test VALUES ('Smyth');
INSERT INTO test VALUES ('Smythe');
INSERT INTO test VALUES ('Smither');
INSERT INTO test VALUES ('Smidt');
INSERT INTO test VALUES ('Smick');
INSERT INTO test VALUES ('Smiff');
COMMIT;

SELECT * FROM test;

SELECT *
FROM test
WHERE SOUNDEX(name) = SOUNDEX('SMITH');
 
SUBSTR
See links at page bottom
 
TO_CHAR
Convert Datatype To String     TO_CHAR(<string_or_column>, <format>)
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS') FROM dual;
 
TRANSLATE
See links at page bottom
 
TREAT
Changes The Declared Type Of An Expression      
 
 
TRIM (variations are LTRIM and RTRIM)
Trim Spaces     TRIM(<string_or_column>)
SELECT '   Dan Morgan    ' FROM dual;

SELECT TRIM('   Dan Morgan   ') FROM dual;
Trim Other Characters     TRIM(<character_to_trim> FROM <string_or_column>)
SELECT TRIM('D' FROM 'Dan Morgan') FROM dual;
Trim By CHR value     TRIM(<string_or_column>)
SELECT ASCII(SUBSTR('Dan Morgan',1,1)) FROM dual;

SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual;
 
UNISTR
Convert String To The National Character Set (either UTF8 or UTF16)     UNISTR('<string>')
SELECT UNISTR(ASCIISTR(CHR(128) || 'Hello' || CHR(255)))
FROM dual;
 
Vertical Bars
Also known as Pipes     <first_string> || <second_string>
SELECT 'Dan' || ' ' || 'Morgan' FROM dual;

with alias

SELECT 'Dan' || ' ' || 'Morgan' NAME FROM dual;
or
SELECT 'Dan' || ' ' || 'Morgan' AS NAME FROM dual;
 
VSIZE
Byte Size     VSIZE(<string_or_column>)
SELECT VSIZE('Dan Morgan') FROM dual;