Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



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

  • string : Specifies the input character string.
  • substring : Specifies the character string whose position is to be returned.
  • position : Optional. Represents the position of a string where the search begins. If omitted, the default value 1 is applied. The first position of the string is specified as 1. If the value is negative, the system counts backward from the end of the string.
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