Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle String Functions

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?
  • YesNo
Previous Oracle Date Functions
Next Oracle Comparison Functions

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

String Functions

  • ASCII
  • CHR
  • CONCAT
  • CONVERT
  • DUMP
  • INSTR
  • INITCAP
  • LENGTH
  • LOWER
  • LPAD
  • LTRIM
  • REPLACE
  • REGEXP_COUNT
  • REGEXP_INSTR
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TRIM
  • UPPER

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.