Data Loader

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

Data Loader
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 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 Locate Function example

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

MySQL Locate Function with optional parameters


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

MySQL INSTR Function

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.

mssql to mysql converter