Oracle String Functions
Version 21c

General Information
Library Note Morgan's Library Page Header
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
Dependencies
STANDARD    
 
ASCII
Get The ASCII Value Of A Character ASCII(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN PLS_INTEGER;
SELECT ASCII('A') FROM dual;

ASCII('A')
----------
        65

SELECT ASCII('Z') FROM dual;
SELECT ASCII('a') FROM dual;
SELECT ASCII('z') FROM dual;
SELECT ASCII(' ') FROM dual;
 
CASE
See link at page bottom
 
CHR
Given an ASCII value returns the corresponding character CHR(n IN PLS_INTEGER) RETURN VARCHAR2;
SELECT(CHR(68) || CHR(65) || CHR(78)) FROM dual;

(CH
---
DAN

SELECT(CHR(68) || CHR(97) || CHR(110)) FROM dual;

(CH
---
Dan
 
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;
 
CONCAT
Concatenate

Overload 1		 standard.CONCAT(
left   VARCHAR2 CHARACTER SET ANY_CS,
right  VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET LEFT%CHARSET;
SELECT CONCAT('Dan ', 'Morgan') FROM dual;

CONCAT('DA
----------
Dan Morgan
Overload 2 CONCAT(
left  IN CLOB CHARACTER SET ANY_CS,
right IN CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET LEFT%CHARSET;
set serveroutput on

DECLARE
 c1 CLOB := TO_CLOB('Dan ');
 c2 CLOB := TO_CLOB('Morgan');
 c3 CLOB;
BEGIN
  SELECT CONCAT('Dan ', 'Morgan')
  INTO c3
  FROM dual;

  dbms_output.put_line(c3);
END;
/
Dan Morgan

PL/SQL procedure successfully completed.
 
CONVERT
Converts a character string from one character set to another

Overload 1		 CONVERT(src IN VARCHAR2 CHARACTER SET ANY_CS, destcset IN VARCHAR2)
RETURN VARCHAR2 CHARACTER SET SRCSTR%CHARSET;
SELECT CONVERT('Ä Ê Í Ó Ø A B C D E', 'US7ASCII')
FROM dual;

CONVERT('ÄEIOOABCDE
-------------------
Z E I O O A B C D E
Overload 2 CONVERT(
src      IN VARCHAR2 CHARACTER SET ANY_CS,
destcset IN VARCHAR2,
srccset  IN VARCHAR2)
RETURN VARCHAR2 CHARACTER SET SRCSTR%CHARSET;
SELECT CONVERT('Ä Ê Í Ó Ø A B C D E','US7ASCII', 'WE8ISO8859P1')
FROM dual;

CONVERT('ÄEIOOABCDE'
--------------------
?? E I O O A B C D E
Overload 3 CONVERT(srcstr IN CLOB CHARACTER SET ANY_CS, dstcsn IN VARCHAR2)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;
TBD
Overload 4 CONVERT(
srcstr IN CLOB CHARACTER SET ANY_CS,
dstcsn IN VARCHAR2,
srccsn IN VARCHAR2)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;
TBD
 
DECODE
See link at page bottom
 
DUMP
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;

TABLE_NAME       DMP
---------------- --------------------------------------------------------
TARGET_XTAB      Typ=1 Len=11: 84,65,82,71,69,84,95,88,84,65,66
EXT_TAB1         Typ=1 Len=8: 69,88,84,95,84,65,66,49
BSR1_EXTTAB      Typ=1 Len=11: 66,83,82,49,95,69,88,84,84,65,66
LOADSHEET_XTAB   Typ=1 Len=14: 76,79,65,68,83,72,69,69,84,95,88,84,65,66
BS_CONSOL1       Typ=1 Len=10: 66,83,95,67,79,78,83,79,76,49

SELECT table_name, DUMP(table_name, 16) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16, 7, 4) DMP FROM user_tables;
 
GREATEST
Returns the "greatest" of multiple values as is demonstrated by the demo at right. GREATEST(pattern IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET pattern%CHARSET;
SELECT GREATEST('Dan Morgan', 'Tom Kyte', 'Jonathan Lewis', 'Richard Foote')
FROM dual;

GREATEST
--------
Tom Kyte
 
INITCAP
Initial Letter Upper Case INITCAP(ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT INITCAP('DAN MORGAN') FROM dual;

INITCAP('D
----------
Dan Morgan
 
INSTR
See link at page bottom
 
INSTRB
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;
SELECT INSTRB('Dan Morgan', ' ', 1, 1) FROM dual;

INSTRB('DANMORGAN','',1,1)
--------------------------
                         4
Overload 2 INSTRB(
str1 IN CLOB CHARACTER SET ANY_CS,
str2 IN CLOB CHARACTER SET STR1%CHARSET,
pos  IN INTEGER := 1, NTH INTEGER := 1)
RETURN INTEGER;
SELECT INSTRB('Dan Morgan', ' ', 1) FROM dual;
 
INSTRC
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;
SELECT INSTRC('Dan Morgan', ' ', 1, 1) FROM dual;

INSTRC('DANMORGAN','',1,1)
--------------------------
                         4
 
INSTR2
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;
SELECT INSTR2('Dan Morgan', ' ', 1, 1) FROM dual;

INSTR2('DANMORGAN','',1,1)
--------------------------
                         4
 
INSTR4
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;
SELECT INSTR4('Dan Morgan', ' ', 1, 1) FROM dual;

INSTR4('DANMORGAN','',1,1)
--------------------------
                         4
 
LEAST
Returns the "least" of multiple values as is demonstrated by the demo at right LEAST(pattern IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET pattern%CHARSET;
SELECT LEAST('Dan Morgan', 'Tom Kyte', 'Jonathan Lewis', 'Richard Foote') FROM dual;

LEAST('DAN
----------
Dan Morgan
 
LENGTH
String length VARCHAR2
Overload 1		 LENGTH(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NATURAL;
SELECT LENGTH('Dan Morgan') FROM dual;

LENGTH('DANMORGAN')
-------------------
                 10
String length CLOB
Overload 2		 LENGTH(ch IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER;
DECLARE
 c CLOB := 'Dan Morgan';
BEGIN
  dbms_output.put_line(TO_CHAR(LENGTH(c)));
END;
/
10

PL/SQL procedure successfully completed.
 
LENGTHB
Returns the string length in bytes. Additional variants  (LENGTHC, LENGTH2, and LENGTH4) are also available. LENGTHB(<char_varchar2_or_clob_value>)
SELECT table_name, LENGTHB(table_name) FROM user_tables WHERE rownum < 3;

TABLE_NAME        LENGTHB(TABLE_NAME)
----------------- -------------------
COURSES_TAB                        11
DEPARTMENT                         10
 
LOWER
Lower Case

Overload 1		 LOWER(ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT LOWER('Dan Morgan') FROM dual;

LOWER('DAN
----------
dan morgan
Lower Case

Overload 2		 LOWER(ch IN CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET ch%CHARSET;
DECLARE
 c CLOB := 'Dan Morgan';
BEGIN
  dbms_output.put_line(LOWER(c));
END;
/
dan morgan

PL/SQL procedure successfully completed.
 
LPAD
Left Pad a string with characters

Overload 1		 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;
TBD
 
LTRIM
Left Trim spaces or characters

Overload 1		 LTRIM(
str1 IN VARCHAR2 CHARACTER SET ANY_CS := ' ',
tset IN VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || LTRIM('   Dan Morgan   ') || '<-' FROM dual;

'->'||LTRIM('DANM
-----------------
->Dan Morgan   <-
Overload 2 LTRIM(str1 IN VARCHAR2 CHARACTER SET ANY_CS := ' ')
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || LTRIM('xxx Dan Morgan   ') || '<-' FROM dual;

SELECT '->' || LTRIM('xxxDan Morgan   ', 'x') || '<-' FROM dual;
Overload 3 LTRIM(
str1 IN CLOB CHARACTER SET ANY_CS,
tset IN CLOB CHARACTER SET STR1%CHARSET)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
Overload 4 LTRIM(str1 IN CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
 
MAX
The "maximum" string based on the current sort parameter MAX(<character_string>) RETURN VARCHAR2;
SELECT MAX(table_name)
FROM user_tables;

MAX(TABLE_NAME)
----------------
TGT_CAPACITY
 
MIN
The "minimum" string based on the current sort parameter MIN(<character_string>) RETURN VARCHAR2;
SELECT MIN(table_name)
FROM user_tables;

MIN(TABLE_NAME)
---------------
BSR1_EXTTAB
 
NCHR
National Character NCHR(n IN PLS_INTEGER) RETURN NVARCHAR2;
SELECT(NCHR(68) || NCHR(65) || NCHR(78)) FROM dual;

(NC
---
DAN

SELECT(NCHR(68) || NCHR(97) || NCHR(110)) FROM dual;

(NC
---
Dan
 
NLSSORT
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
 
NLS_INITCAP
NLS Initial Letter Upper Case

Overload 1		 NLS_INITCAP(
ch    IN VARCHAR2 CHARACTER SET ANY_CS,
parms IN VARCHAR2 CHARACTER SET ch%CHARSET)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT NLS_INITCAP('DAN MORGAN', 'NLS_SORT = XGerman')
FROM dual;

NLS_INITCA
----------
Dan Morgan
NLS Initial Letter Upper Case

Overload 2		 NLS_INITCAP(ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT NLS_INITCAP('DAN MORGAN')
FROM dual;

NLS_INITCA
----------
Dan Morgan
 
NLS_LOWER
NLS Lower Case

Overload 1		 NLS_LOWER(
ch    IN VARCHAR2 CHARACTER SET ANY_CS,
parms IN VARCHAR2 CHARACTER SET ch%CHARSET)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT NLS_LOWER('Dan Morgan', 'NLS_SORT = XFrench')
FROM dual;

NLS_LOWER(
----------
dan morgan
NLS Lower Case

Overload 2		 NLS_LOWER(ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT NLS_LOWER('Dan Morgan')
FROM dual;

NLS_LOWER(
----------
dan morgan
NLS Lower Case

Overload 3		 NLS_LOWER(
ch    IN CLOB CHARACTER SET ANY_CS,
parms IN VARCHAR2 CHARACTER SET ch%CHARSET)
RETURN CLOB CHARACTER SET ch%CHARSET;
DECLARE
 c CLOB := 'Dan Morgan';
BEGIN
  dbms_output.put_line(NLS_LOWER(c, 'NLS_SORT = XFrench'));
END;
/
dan morgan

PL/SQL procedure successfully completed.
NLS Lower Case

Overload 4		 NLS_LOWER(ch IN CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET ch%CHARSET;
DECLARE
 c CLOB := 'Dan Morgan';
BEGIN
  dbms_output.put_line(NLS_LOWER(c));
END;
/
dan morgan

PL/SQL procedure successfully completed.
 
NLS_UPPER
NLS Upper Case

Overload 1		 NLS_UPPER(
ch    IN VARCHAR2 CHARACTER SET ANY_CS,
parms IN VARCHAR2 CHARACTER SET ch%CHARSET)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT NLS_UPPER('Dan Morgan', 'NLS_SORT = XDanish')
FROM dual;
NLS Upper Case

Overload 2		 NLS_UPPER(ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT NLS_UPPER('Dan Morgan')
FROM dual;
NLS Upper Case

Overload 3		 NLS_UPPER(
ch    IN CLOB CHARACTER SET ANY_CS,
parms IN VARCHAR2 CHARACTER SET ch%CHARSET)
RETURN CLOB CHARACTER SET ch%CHARSET;
DECLARE
 c CLOB := 'Dan Morgan';
BEGIN
  dbms_output.put_line(NLS_UPPER(c, 'NLS_SORT = XDanish'));
END;
/
DAN MORGAN

PL/SQL procedure successfully completed.
NLS Upper Case

Overload 4		 NLS_UPPER(ch IN CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET ch%CHARSET;
DECLARE
 c CLOB := 'Dan Morgan';
BEGIN
    dbms_output.put_line(NLS_UPPER(c));
END;
/
DAN MORGAN

PL/SQL procedure successfully completed.
 
Quote Delimiters
q used to define a quote delimiter for PL/SQL q'<delimiter><string><delimiter>';
set serveroutput on

DECLARE
 s0 VARCHAR2(20);
BEGIN
  s0 := 'This isn't cool';
  dbms_output.put_line(s0);
END;
/
ERROR:
ORA-01756: quoted string not properly terminated

DECLARE
 s0 VARCHAR2(20);
BEGIN
  s0 := 'This isn''t cool';
  dbms_output.put_line(s0);
END;
/
This isn't cool

PL/SQL procedure successfully completed.

DECLARE
 s1 VARCHAR2(20);
 s2 VARCHAR2(20);
 s3 VARCHAR2(20);
BEGIN
  s1 := q'[Isn't this cool]';
  s2 := q'"Isn't this cool"';
  s3 := q'|Isn't this cool|';

  dbms_output.put_line(s1);
  dbms_output.put_line(s2);
  dbms_output.put_line(s3);
END;
/
Isn't this cool
Isn't this cool
Isn't this cool

PL/SQL procedure successfully completed.
 
REPLACE
See link at page bottom
 
REVERSE
Reverse the ordering of characters within a string REVERSE(<string_or_column>) RETURN VARCHAR2;
SELECT REVERSE('Dan Morgan') FROM dual;

REVERSE('D
----------
nagroM naD

SELECT DUMP('Dan Morgan') FROM dual;

DUMP('DANMORGAN')
-------------------------------------------------
Typ=96 Len=10: 68,97,110,32,77,111,114,103,97,110

SELECT DUMP(REVERSE('Dan Morgan')) FROM dual;

DUMP(REVERSE('DANMORGAN'))
-------------------------------------------------
Typ=96 Len=10: 110,97,103,114,111,77,32,110,97,68
 
RPAD
Right Pad a string with characters

Overload 1		 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;
TBD
 
RTRIM
Right Trim characters from a string

Overload 1		 RTRIM(
str1 IN VARCHAR2 CHARACTER SET ANY_CS := ' ',
tset IN VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || RTRIM('   Dan Morganxxx') || '<-' FROM dual;
SELECT '->' || RTRIM('   Dan Morganxxx', 'xxx') || '<-' FROM dual;
Overload 2 RTRIM(str1 IN VARCHAR2 CHARACTER SET ANY_CS := ' ')
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' || RTRIM('   Dan Morgan   ') || '<-' FROM dual;
Overload 3 RTRIM(
str1 IN CLOB CHARACTER SET ANY_CS,
tset IN CLOB CHARACTER SET STR1%CHARSET)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
Overload 4 RTRIM(str1 IN CLOB CHARACTER SET ANY_CS) RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
 
SOUNDEX
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');
 
SUBSTR
See link at page bottom
 
SUBSTRB
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;
See the Demos on the Substring Page
 
SUBSTRC
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;
See the Demos on the Substring Page
 
SUBSTR2
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;
See the Demos on the Substring Page
 
SUBSTR4
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;
See the Demos on the Substring Page
 
TRANSLATE
See link at page bottom
 
TRIM (variations are LTRIM and RTRIM)
Trim spaces or characters from a string

Overload 1		 TRIM(v IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET v%CHARSET;
SELECT '   Dan Morgan    ' FROM dual;

SELECT TRIM('   Dan Morgan   ') FROM dual;

SELECT TRIM('D' FROM 'Dan Morgan') FROM dual;

SELECT ASCII(SUBSTR('Dan Morgan',1,1)) FROM dual;

SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual;

SELECT TRIM(LEADING ',' FROM ',TEST,') FROM dual;

SELECT TRIM(TRAILING ',' FROM ',TEST,') FROM dual;
Overload 2 TRIM(v IN CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET v%CHARSET;
set serveroutput on

DECLARE
 cv CLOB := 'This is a test';
BEGIN
  cv := TRIM(LEADING 'T' FROM cv);
  dbms_output.put_line(cv);
END;
/
his is a test

PL/SQL procedure successfully completed.
 
UPPER
Upper Case

Overload 1		 UPPER(ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT UPPER('Dan Morgan') FROM dual;
Upper Case

Overload 2		 UPPER(ch IN CLOB CHARACTER SET ANY_CS)
RETURN CLOB CHARACTER SET ch%CHARSET;
DECLARE
 c CLOB := 'Dan Morgan';
BEGIN
  dbms_output.put_line(UPPER(c);
END;
/
 
Vertical Bars
Also known as Pipes '||' (
left  IN VARCHAR2 CHARACTER SET ANY_CS,
right IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET LEFT%CHARSET;
SELECT 'Dan' || ' ' || 'Morgan' FROM dual;

with alias

SELECT 'Dan' || ' ' || 'Morgan' NAME FROM dual;
or
SELECT 'Dan' || ' ' || 'Morgan' AS NAME FROM dual;
 
VSIZE
Returns the number of bytes required by a value VSIZE(e IN VARCHAR2) RETURN NUMBER;
SELECT VSIZE('Dan Morgan') FROM dual;

VSIZE('DANMORGAN')
------------------
                10

Related Topics
Analytic Functions
Built-in Functions
Built-in Packages
CASE
CAST
Data Mining Functions
Date Functions
DBMS_LOB
DECODE
INSTRING
Miscellaneous Functions
Numeric Functions
OLAP Functions
Operators (Built-in)
Regular Expressions
REPLACE
SUBSTRING
Timestamp Functions
TRANSLATE
Undocumented Oracle
XML Functions
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx