Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Product Page - Migration to Oracle Request SQLWays

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

LENGTH

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

SUBSTR

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

INSTR

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:

  • Return the first occurrence of 'CD'
SELECT INSTR('ABCDCD', 'CD') c1 FROM dual;

c1
------
3
  • Return the second occurrence of 'CD'
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


Ispirer Home Page Ispirer SQLWays Product Page - Migration to Oracle Request SQLWays

sqlways/oracle/string-functions.txt · Last modified: March 20, 2013, 04:00:02 AM (external edit)
 
© 1999-2018, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement