Different Substring Function Result In PHP and MySQL

PHP and MySQL were long a couple in web development. Both of them has a same string manipulation function called substr() which has the same functionality to cut the specified string into various sub-string.

For example when I have a simple 11 characters string

"I own a car"

And I want to get the substring “car”, whose string start (offset) from the 9th character of the string, can be captured by PHP substr() function:

<?php echo substr("I own a car", 8); ?>

But why 8 and not 9? Does the c character from “car” really starts from 9th character? Yes, indeed. PHP starts the characters indexing of the string from 0, so that the first character has the index 0, not 1. So if you would like to capture the whole string using substr() function in PHP you can set the 2nd argument of it to 0.

OK, now I understand how to capture substring in PHP. Does MySQL do the same? The answers is Yes and No. Yes, MySQL does the same functionality with substr() function, but No, MySQL has different string indexing number compared to PHP.

MySQL start the indexing of characters in a string from 1. So to SELECT “car” from “I own a car” string you have to specify the index offset to 9 like the following example:

SELECT SUBSTR('I own a car', 9);

That is it! I hope this explain someone who confused why MySQL return different result in SUBSTR() function compared to PHP. The same string, the same function, the same result, different arguments.