INSTR Function

Description

The INSTR function, similarly to the POSITION, returns the position of a substring within string; the position. For the INSTR function, you can specify the starting position of the search for substring to make it possible to search for duplicate substring.

Note that the function calculates the starting position and the length of the character string in bytes, not in characters. For a multi-byte character set, the number of bite representing onc character is different, so the return value may not be the same.

Syntax

INSTR( string , substring [, position] )

 

string , substring :

• character string

NULL

position :

INT

NULL

Example

--character set is euc-kr for Korean characters

--it returns position of the first 'b'

SELECT INSTR ('12345abcdeabcde','b');

   instr('12345abcdeabcde', 'b', 1)

===================================

                                  7

 

-- it returns position of the first '나' on double byte charset

SELECT INSTR ('12345가나다라마가나다라마', '나' );

   instr('12345가나다라마가나다라마', '나', 1)

=================================

                                8

 

-- it returns position of the second '나' on double byte charset

SELECT INSTR ('12345가나다라마가나다라마', '나', 16 );

   instr('12345가나다라마가나다라마', '나', 16)

=================================

                               18

 

--it returns position of the 'b' searching from the 8th position

SELECT INSTR ('12345abcdeabcde','b', 8);

   instr('12345abcdeabcde', 'b', 8)

===================================

                                 12

 

--it returns position of the 'b' searching backwardly from the end

SELECT INSTR ('12345abcdeabcde','b', -1);

   instr('12345abcdeabcde', 'b', -1)

====================================

                                  12

 

--it returns position of the 'b' searching backwardly from a specified position

SELECT INSTR ('12345abcdeabcde','b', -8);

   instr('12345abcdeabcde', 'b', -8)

====================================

                                   7