Getting Substrings in MySQL

I recently had a bit of trouble with substrings in MySQL so I thought I’d share my findings

I wanted to check in my film times database which titles ended with “3D” and then update the record to remove “3D” (and the space beforehand) from the title and instead add it to a boolean field to indicate the movie is a 3D showing.

MySQL provides a substring function which allows you to start at the end of the string like PHP does but does not allow you to specify a negative length. The workaround is to start the substring at 0, get the length of the string using the length function, then subtract the number of characters you want to see. For example:

UPDATE films SET title = SUBSTR(title,0,length(title) - 3) WHERE SUBSTR(title,2) = “3D”

Using this then titles such as “Monsters University 3D” should be renamed to “Monsters University”

NOTE: SUBSTR and SUBSTRING are the same fucntion in MySQL