MSSQL CHARINDEX Function and Its Equivalent in MySQL

CHARINDEX in MSSQL searches a string for the occurrence of a sequence of characters and returns the position of its first occurrence if it exists, otherwise returns 0. The same functionality in MySQL is provided by the LOCATE or INSTR functions.

Syntax:

CHARINDEX ( expressionToFind, expressionToSearch [ , start_location ] )

MSSQL Example

select CHARINDEX('A','An Apple');
------------------
1

Charindex Function example

select CHARINDEX('A','An Apple',2)
------------------------------
4

MSSQL CHARINDEX function example

select CHARINDEX('b','An Apple');
--------------------------
0

MySQL Example

In MySQL, the same functionality is provided by the LOCATE or INSTR functions.

LOCATE Syntax

LOCATE(substr, str)
LOCATE(substr, str, pos)

Where:

substr:  the string to find in str
str:     the string to search within
pos:     optional starting position to begin searching

Example:

mysql> select locate('a','An Apple');
---------------
1

MySQL Locate Function example

mysql> select locate('a','An Apple',2)
-----------
4

MySQL Locate Function with optional parameters

MySQL INSTR Function

The INSTR function provides the same functionality, except you can't specify the position to start searching.

Syntax:

INSTR(str, substr)

Where:

str:     the string to search within
substr:  the string to find in str

Example:

mysql> select instr('An Apple','a');
-----------------
1

MySQL INSTR Function

Back to Converting Functions from MSSQL to MySQL

The MSSQL to MySQL Converter will automatically convert MSSQL functions to their equivalent in MySQL while converting views.