MySQL substring start is one based

To my surprise, MySQL SUBSTRING functions start position is one based. A start position of ‘0’ will return an empty string.

If you would like to get the first 100 characters of a column named ‘my_column’ you will need to use SUBSTRING(‘my_column’, 1, 100). Using SUBSTRING(‘my_column’, 0, 100). will return an empty string.

Postgres and Microsoft SQL server also seem to be one based, but, have different behaviour when a ‘0’ is passed for the start position.

Leave a Reply

Your email address will not be published. Required fields are marked *