arpit1611 arpit1611 - 5 months ago 27
MySQL Question

MySQL get all characters before specific character, If not then whole value

I need to make a query in which I have to get the string before the specific character. In my case its dot(.). And if there isn't any dot, then whole value should get returned.

If values are

Titanic.Movie
and
Avatar
, then expected output is
Titanic
and
Avatar
.

What I tried is giving me the substring before dot(.), but if there isn't any dot then it's returning blank value.

SELECT
LEFT(movie, INSTR(movie, '.') - 1)
FROM
movie_list;

Answer

You can use SUBSTRING_INDEX:

SELECT SUBSTRING_INDEX(movie, '.', 1) movie
FROM movie_list;