ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
This page is a collection of built-in Oracle Database functions used to evaluate and manipulate strings.
There are functions not listed here such as CASE, DECODE, REPLACE, SUBSTRING, and TRANSLATE and you will find links to them at the bottom of the page.
Additional undocumented string related functions can be found on the Undocumented Oracle page also linked at page bottom.
Note
Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions
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');
Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value
Overload 1 (Overload 2 is NUMBER and Overload 3 is DATE)
DUMP(
e IN VARCHAR2 CHARACTER SET ANY_CS, -- expression
df IN PLS_INTEGER := NULL, -- return format
sp IN PLS_INTEGER := NULL, -- starting position
len IN PLS_INTEGER := NULL) -- length
RETURN VARCHAR2;
8
Octal
10
Decimal
16
Hexidecimal
17
Single Characters
1008
Octal notation with the character set name
1010
Decimal notation with the character set name
1016
Hexadecimal notation with the character set name
1017
Single characters with the character set name
col dmp format a50
SELECT table_name, DUMP(table_name) DMP FROM user_tables
WHERE rownum < 6;
Location of a string, within another string, in bytes
Overload 1
INSTRB(
str1 IN VARCHAR2 CHARACTER SET ANY_CS, -- test string
str2 IN VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
pos IN PLS_INTEGER := 1, -- position
nth IN POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
Location of a string, within another string, in Unicode complete characters
INSTRC(
str1 IN VARCHAR2 CHARACTER SET ANY_CS, -- test string
str2 IN VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
pos IN PLS_INTEGER := 1, -- position
nth IN POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
Location of a string, within another string, in UCS2 code points
INSTR2(
str1 IN VARCHAR2 CHARACTER SET ANY_CS, -- test string
str2 IN VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
pos IN PLS_INTEGER := 1, -- position
nth IN POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
Location of a string, within another string, in UCS4 code points
INSTR4(
str1 IN VARCHAR2 CHARACTER SET ANY_CS, -- test string
str2 IN VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
pos IN PLS_INTEGER := 1, -- position
nth IN POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
LPAD(
str1 IN VARCHAR2 CHARACTER SET ANY_CS,
len IN PLS_INTEGER,
PAD IN VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25, 'x') FROM dual;
LPAD('DANMORGAN',25,'X')
-------------------------
xxxxxxxxxxxxxxxDan Morgan
Overload 2
LPAD(
str1 IN VARCHAR2 CHARACTER SET ANY_CS,
len IN PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25) FROM dual;
LPAD('DANMORGAN',25)
-------------------------
Dan Morgan
Overload 3
LPAD(
str1 IN CLOB CHARACTER SET ANY_CS,
len IN NUMBER,
PAD IN CLOB CHARACTER SET STR1%CHARSET)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
Overload 4
LPAD(
str1 IN CLOB CHARACTER SET ANY_CS,
len IN INTEGER)
RETURN CLOB CHARACTER SET STR1%CHARSET;
Returns the string of bytes used to sort a string. The string returned is of the RAW data type
Overload 1
NLSSORT(c IN VARCHAR2 CHARACTER SET ANY_CS) RETURN RAW;
conn uwclass/uwclass@pdbdev
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;
NAME
------------------------------
Gaardiner
Gaberd
Gâberd
SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');
NAME
------------------------------
Gaberd
Gâberd
Gaardiner
SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = BINARY_CI');
NAME
------------------------------
Gaardiner
Gaberd
Gâberd
Overload 2
NLSSORT(c IN VARCHAR2 CHARACTER SET ANY_CS, c2 IN VARCHAR2) RETURN RAW;
SELECT *
FROM test
ORDER BY NLSSORT(name);
NAME
------------------------------
Gaardiner
Gaberd
Gâberd
RPAD(
str1 IN VARCHAR2 CHARACTER SET ANY_CS,
len IN PLS_INTEGER,
pad IN VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25, 'x') FROM dual;
Overload 2
RPAD(
str1 IN VARCHAR2 CHARACTER SET ANY_CS,
len IN PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25) ||'<-' FROM dual;
Overload 3
RPAD(
str1 IN CLOB CHARACTER SET ANY_CS,
len IN INTEGER,
pad IN CLOB CHARACTER SET STR1%CHARSET)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
Overload 4
RPAD(
str1 IN CLOB CHARACTER SET ANY_CS,
len IN INTEGER)
RETURN CLOB CHARACTER SET STR1%CHARSET;
Returns a character string containing 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(ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
conn uwclass/uwclass@pdbdev
CREATE TABLE test (
namecol VARCHAR2(15));
INSERT INTO test (namecol) VALUES ('Smith');
INSERT INTO test (namecol) VALUES ('Smyth');
INSERT INTO test (namecol) VALUES ('Smythe');
INSERT INTO test (namecol) VALUES ('Smither');
INSERT INTO test (namecol) VALUES ('Smidt');
INSERT INTO test (namecol) VALUES ('Smick');
INSERT INTO test (namecol) VALUES ('Smiff');
COMMIT;
SELECT name, SOUNDEX(namecol) FROM test;
-- Thanks Frank van Bortel for the idea for the above
SELECT *
FROM test
WHERE SOUNDEX(namecol) = SOUNDEX('SMITH');
Returns a substring counting bytes rather than characters
Overload 1
SUBSTRB(
str1 IN VARCHAR2 CHARACTER SET ANY_CS,
pos IN PLS_INTEGER, -- starting byte position
len IN PLS_INTEGER := 2147483647) -- number of bytes
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
See the Demos on the Substring Page
Overload 2
SUBSTR(
str1 IN CLOB CHARACTER SET ANY_CS,
posS IN INTEGER,
len IN INTEGER := 18446744073709551615)
RETURN CLOB CHARACTER SET STR1%CHARSET;
Returns a substring within another string, using Unicode code points
SUBSTRC(
str1 IN VARCHAR2 CHARACTER SET ANY_CS,
pos IN PLS_INTEGER, -- starting position
len IN PLS_INTEGER := 2147483647) -- number of unicode complete characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
Returns a substring within another string, using UCS2 code points
SUBSTR2(
str1 IN VARCHAR2 CHARACTER SET ANY_CS,
pos IN PLS_INTEGER, -- starting position
len IN PLS_INTEGER := 2147483647) -- number of UCS2 code points
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
Returns a substring within another string, using UCS4 code points
SUBSTR4(
str1 IN VARCHAR2 CHARACTER SET ANY_CS,
pos IN PLS_INTEGER, -- starting position
len IN PLS_INTEGER := 2147483647) -- number of UCS4 code points
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;