Oracle String Functions

This tutorial provides you the most commonly used Oracle string functions that help you manipulate character strings more effectively.

FunctionExampleResultPurpose
ASCIIASCII(‘A’)65Returns an ASCII code value of a character.
CHRCHR(’65’)‘A’Converts a numeric value to its corresponding ASCII character.
CONCATCONCAT(‘A’,’BC’)‘ABC’Concatenate two strings and return the combined string.
CONVERTCONVERT( ‘Ä Ê Í’, ‘US7ASCII’, ‘WE8ISO8859P1’ ) ‘A E I’Convert a character string from one character set to another.
DUMPDUMP(‘A’)Typ=96 Len=1: 65Return a string value (VARCHAR2) that includes the datatype code, length measured in bytes, and internal representation of a specified expression.
 INITCAP INITCAP(‘hi  there’)‘Hi There’Converts the first character in each word in a specified string to uppercase and the rest to lowercase.
 INSTRINSTR( ‘This is a playlist’, ‘is’)3Search for a substring and return the location of the substring in a string
 LENGTHLENGTH(‘ABC’)3Return the number of characters (or length) of a specified string
LOWERLOWER(‘Abc’)‘abc’Return a string with all characters converted to lowercase.
LPADLPAD(‘ABC’,5,’*’)‘**ABC’Return a string that is left-padded with the specified characters to a certain length.
LTRIMLTRIM(‘ ABC ‘)‘ABC  ‘Remove spaces or other specified characters in a set from the left end of a string.
REGEXP_COUNTREGEXP_COUNT(‘1 2 3 abc’,’\d’)3Return the number of times a pattern occurs in a string.
REGEXP_INSTRREGEXP_INSTR( ‘Y2K problem’,’\d+’)2Return the position of a pattern in a string.
REGEXP_LIKEREGEXP_LIKE( ‘Year of 2017′,’\d+’ )trueMatch a string based on a regular expression pattern.
REGEXP_REPLACEREGEXP_REPLACE( ‘Year of 2017′,’\d+’, ‘Dragon’ ) ‘Year of Dragon’Replace substring in a string by a new substring using a regular expression.
REGEXP_SUBSTRREGEXP_SUBSTR( ‘Number 10’, ‘\d+’ ) 10Extract substrings from a string using a pattern of a regular expression.
REPLACEREPLACE(‘JACK AND JOND’,’J’,’BL’); ‘BLACK AND BLOND’Replace all occurrences of a substring by another substring in a string.
RPAD RPAD(‘ABC’,5,’*’)‘ABC**’Return a string that is right-padded with the specified characters to a certain length.
RTRIMRTRIM(‘ ABC ‘)‘ ABC’Remove all spaces or specified character in a set from the right end of a string.
SOUNDEXSOUNDEX(‘sea’)‘S000’Return a phonetic representation of a specified string.
SUBSTRSUBSTR(‘Oracle Substring’, 1, 6 )‘Oracle’Extract a substring from a string.
TRANSLATE TRANSLATE(‘12345’, ‘143’, ‘bx’) ‘b2x5’Replace all occurrences of characters by other characters in a string.
TRIMTRIM(‘ ABC ‘)‘ABC’Remove the space character or other specified characters either from the start or end of a string.
UPPERUPPER(‘Abc’)‘ABC’Convert all characters in a specified string to uppercase.
Was this tutorial helpful?