Tuesday, 30 July 2019

How the INSTR() Function Works in MySQL

The INSTR() function returns the position of the first occurrence of a substring within a string. Basically, it does the same thing that the two-argument syntax of the LOCATE()function does (except that the order of the arguments is reversed).

Syntax

The syntax goes like this:
INSTR(str,substr)
Where str is the string you want to search, and substr is the substring that you’re searching for.

Example 1 – Basic Usage

SELECT INSTR('Cats and dogs like to run', 'dogs') AS Result;
Result:
+--------+
| Result |
+--------+
|     10 |
+--------+
In this case, the string dogs starts at position 10.

Example 2 – Not Case Sensitive

It’s not case sensitive:
SELECT 
  INSTR('Cats and dogs like to run', 'DOGS') AS 'Result 1',
  INSTR('Cats and DOGS like to run', 'dogs') AS 'Result 2';
Result:
+----------+----------+
| Result 1 | Result 2 |
+----------+----------+
|       10 |       10 |
+----------+----------+

Example 3 – Partial Match

It doesn’t matter if your search term only represents part of a word, it will still match (after all, you’re simply searching for a substring within a string):
SELECT INSTR('Cats and dogs like to run', 'do') AS Result;
Result:
+--------+
| Result |
+--------+
|     10 |
+--------+
A space is still part of the string. So you can search for the first space if you need to:
SELECT INSTR('Cats and dogs like to run', ' ') AS Result;
Result:
+--------+
| Result |
+--------+
|      5 |
+--------+

Example 4 – First Occurrence Only

Remember, only the position of the first occurrence is returned:
SELECT INSTR('Cats and dogs like to run', 'a') AS Result;
Result:
+--------+
| Result |
+--------+
|      2 |
+--------+

Example 5 – When the Substring Can’t be Found

If the substring isn’t found, a result of 0 is returned:
SELECT INSTR('Cats and dogs like to run', 'rabbit') AS Result;
Result:
+--------+
| Result |
+--------+
|      0 |
+--------+

Example 6 – Incorrect Parameter Count

If you omit any of the parameters, you’ll get an error:
SELECT INSTR('Cats and dogs like to run') AS Result;
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'INSTR'
You’ll also get an error if you provide too many parameters:
SELECT INSTR('Cats and dogs like to run', 'dogs', 'cats') AS Result;
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'INSTR'

0 comments:

Post a Comment