INSTR in SQL - sql - sql tutorial - learn sql




 representation of postion of characters in instr command functions
  • The INSTR functions (INSTR, INSTRB, INSTRC, INSTR2, and INSTR4) searches a string for a substring using characters and returns the position in the string that is the first character of a specified occurrence of the substring. The functions vary in how they determine the position of the substring to return.
    • INSTR calculates lengths using characters as defined by the input character set.
    • INSTRB calculates lengths using bytes.
    • INSTRC calculates lengths using Unicode complete characters.
    • INSTR2 calculates lengths using UCS2 code points.
    • INSTR4 calculates lengths using UCS4 code points.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Return Value

  • The INSTR function in SQL is used to find the starting location of a pattern in a string.
  • This function is available in MySQL and Oracle, though they have slightly different syntaxes:
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Syntax

  • The syntax for the INSTR function is as follows:

MySQL:

INSTR (str, pattern)
  • Find the staring location of pattern in string str.

Oracle:

Region_Name Store_Name
East Boston
East New York
West Los Angeles
West San Diego
INSTR (str, pattern, [starting position, [nth occurrence]])
  • Find the starting location of the nth occurrence of pattern beginning in the starting position-th position in string str.

Examples

  • We use the following table for our examples.

Table Geography

Example 1 (both Oracle and MySQL)

SELECT INSTR (Store_Name, 'o') 
FROM Geography 
WHERE Store_Name = 'Los Angeles';

Result:

  • 2
  • The first occurrence of 'o' is the second character in the word 'Los Angeles.'

Example 2 (both Oracle and MySQL)

SELECT INSTR (Store_Name, 'p') 
FROM Geography 
WHERE Store_Name = 'Los Angeles';

Result:

  • 0
  • In this case, the pattern p does not exist in string 'Los Angeles,' so the function returns 0.

Example 3 (Oracle only)

SELECT INSTR(Store_Name,'e', 1, 2)  
FROM Geography 
WHERE Store_Name = 'Los Angeles';

Result:

  • 10
  • In this case, we are looking for the second occurrence of the character 'e' in the word 'Los Angeles,' and we start the start with the first character of the word.
  • The function returns 10 as the second occurrence of 'e' is in the 10th position.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Arguments:

string

  • The text expression to search.

substring

  • The string to search for.

position

  • A nonzero INTEGER indicating where in string the function begins the search. INSTR calculates position using characters as defined by the input character set. INSTRB calculates position using bytes. INSTRC calculates position using Unicode complete characters. INSTR2 calculates position using UCS2 code points. INSTR4 calculates position using UCS4 code points.
  • When position is negative, then INSTR counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the beginning of string.

occurrence

  • An INTEGER indicating which occurrence of string the function should search for. The value of occurrence must be positive. The default values of occurrence is 1, meaning the function searches for the first occurrence of substring.

Examples

  • Example 7-127 Using Character Position to Search Forward to Find the Position of a Substring
  • The following example searches the string "Corporate Floor", beginning with the third character, for the string "or". It returns the position in "Corporate Floor" at which the second occurrence of "or" begins.
SHOW INSTR('Corporate Floor','or', 3, 2)
14
  • Example 7-128 Using Character Position to Search Backward to Find the Position of a Substring
  • In this next example, the function counts backward from the last character to the third character from the end, which is the first "o" in "Floor". The function then searches backward for the second occurrence of "or", and finds that this second occurrence begins with the second character in the search string.
SHOW INSTR('Corporate Floor','or', -3, 2)
2
  • Example 7-129 Using a Double-Byte Character Set to Find the Position of a Substring
  • This example assumes a double-byte database character set.
SHOW INSTRB('Corporate Floor','or',5,2) 

output

27  

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to INSTR in SQL