Oracle - String Functions

This article describes string functions in Oracle.

Overview

String Function	Description
LENGTH()	Length of string
SUBSTR()	Substring at the specified position and length
INSTR()	Position of substring in string

Oracle - LENGTH
Syntax	LENGTH(string)
Description	Returns the number of characters in string. The return type is NUMBER
Notes	if string is CHAR, the length includes trailing blanks. If string is NULL, returns NULL

Example:

SELECT LENGTH('Boston') "Length" FROM dual;

Length 
------
     6

Oracle LENGTH - Equivalents in Other Databases

Database	Equivalent
SQL Server	LEN(string) - Excludes trailing blanks

Oracle - SUBSTR
Syntax	SUBSTR(string, start_position [, length])
Description	Returns substring of string at the specified position start_position and length characters long.
Default Values	if length is omitted, returns all characters to the end of string

Example:

SELECT SUBSTR('NEW YORK', 5, 4) "Substr" FROM dual;
 
Substr
------
YORK

Oracle SUBSTR - Equivalents in Other Databases

Database	Equivalent
SQL Server	SUBSTRING(string, start_position, length) - length is required

Oracle - INSTR
Syntax	INSTR(string, substring [, start_position, occurrence])
Description	INSTR searches string for substring. start_position indicates the position to begin the search. occurrence specifies which occurrence to search
Return Value	INSTR returns the position of the first character or 0 if the substring is not found. The return type is NUMBER
Default Values	start_position is 1, occurrence is 1

Examples:

SELECT INSTR('ABCDCD', 'CD') c1 FROM dual;

c1
------
3

SELECT INSTR('ABCDCD', 'CD', 1, 2) c1 FROM dual;

c1
------
5

Oracle INSTR - Equivalents in Other Databases

Database	Equivalent
MySQL	LOCATE(substring, string, start_position) - Different parameter order, no option to specify occurrence number