MSSQL CHARINDEX Function and it's Equivalent in MySQL

CHARINDEX function in MSSQL Searches a String for the occurence of a sequence of characters or a string and returns the position of it's first occurence if it exists, otherwise returns 0 . The same functionality in MySQL is provided by LOCATE or INSTR functions

Syntax

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )   

MSSQL Example

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

------------------
1

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

------------------------------
4

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

--------------------------
0

 

See Also:

Top tools for converting MS SQL Server to MySQL

 

MySQL Example

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

Here is the syntax of MySQL Locate Function

LOCATE(substr,str)

 LOCATE(substr,str,pos)

Where

substr:  It's the string needs to be found in the str
str:     It's the string in which substr is searched for
pos:     Optional, you can even specify the starting position to start searching in the str

Here is the example

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

---------------
1

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

-----------
4

MySQL INSTR Function

The INSTR function also provides the same functionality except you cannot specify the position to start searching in the string.

Here is the syntax of the INSTR function

INSTR(str,substr)

Where

str:     It's the string in which substr is searched for
substr:  It's the string needs to be found in the str

INSTR Example

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

-----------------
1

Back to Converting Functions from MSSQL to MySQL

The MSSQL to MySQL  Converter will automatically convert the MSSQL functions to it's equivalent in MySQL while converting veiws.