Archive for the ‘String Functions’ Category


Built-in functions/operators available for manipulating Strings

The most useful ones are LENGTH, SUBSTR, INSTR, and ||:

  • LENGTH(str) returns the length of str in characters.
  • SUBSTR(str,m,n) returns a portion of str, beginning at character m, n characters long. If n is omitted, all characters to the end of str will be returned.
  • INSTR(str1,str2,n,m) searches str1 beginning with its n-th character for the m-th occurrence of str2 and returns the position of the character in str1 that is the first character of this occurrence.
  • str1 || str2 returns the concatenation of str1 and str2.

The example below shows how to convert a string name of the format 'last, first' into the format 'first last':

SUBSTR(name,INSTR(name,’,’,1,1)+2) ||’ ‘|| SUBSTR(name, 1, INSTR(name,’,’,1,1)-1)

For case-insensitive comparisons, first convert both strings to all upper case using Oracle’s built-in function upper() (or all lower case using lower()).


The syntax for the substr function is:

substr( string, start_position, [ length ] )

string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

substr(‘This is a test’, 6, 2) would return ‘is’

substr(‘This is a test’, 6) would return ‘is a test’

substr(‘TechOnTheNet’, 1, 4) would return ‘Tech’

substr(‘TechOnTheNet’, -3, 3) would return ‘Net’


The syntax for the instr Oracle function is:

instr( string1, string2 [, start_position [, nth_appearance ] ] )

string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.


Query returns string after the 6th occurence of ‘\’ in full path of data file name

select substr(name,instr(name,’\’,1,6)+1) from v$datafile



The syntax for the replace function is:

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.

replace(‘123tech123’, ‘123’); would return ‘tech’

replace(‘222tech’, ‘2’, ‘3’);    would return ‘333tech’


TRANSLATE(<string>, <‘list_to_match’>,<‘replacements_list’>)

This demo replaces all commas with vertical bars.

SELECT TRANSLATE(‘comma,delimited,list’  ,   ‘,‘    , ‘|‘)
FROM dual;

The following takes a DNA sequence and returns its complement.

FROM dual;

In this demo the number of vowels in the string is counted. First With clause the whole line is stored in “data” pseudo table and then letter”x” will be translated to “x” (which won’t happen as there is no “x” in the string line) and all vowels in the line are translated to nothng as no substitute is given for them so they would simply be eliminated. All eliminating all vowels from “line” string, length of the remaining string is calculated which is 15. In the end, the number of vowels in the line string are calculated by taking the length of whole “line” string (23) minus the length of “line” string without vowels (15) which comes out to be 8.

WITH data AS (SELECT ‘Whose line is it anyway’ line FROM dual)
SELECT LENGTH(line)-LENGTH(TRANSLATE(line,’xaeiou’,’x’)) nbVowels
FROM data;

TRANSLATE(line,’xaeiou’,’x’)                     = Whs ln s t nywy

LENGTH(TRANSLATE(line,’xaeiou’,’x’) =  15

LENGTH(line)  = 23

LENGTH(line)-LENGTH(TRANSLATE(line,’xaeiou’,’x’))  = 23  –  15 = 8