Shafizadeh Shafizadeh - 4 months ago 7
SQL Question

How can I remove the format part of the string?

I have this table:

// image_path
+----+-----------------------------+
| id | path |
+----+-----------------------------+
| 1 | 3498uhrf34i098jf349jf43.png |
| 2 | mfij948t5498hj43h9f34p0.jpg |
| 3 | fcnh9348jmof9348i34f435.png |
| 4 | 3049jr430okf43p0ijfr43f.gif |
| 5 | oi3094jfo439ijf430f443r.jpg |
| 6 | 3f0irj03409iko4p3rfo04f.png |
+----+-----------------------------+


And here is my query:

SELECT * FROM image_path WHERE path = :token;
// ^^^^ removing everything from the right side until dot


So here is an example:

SELECT * FROM image_path WHERE path = 'fcnh9348jmof9348i34f435';
/* output:
+----+-----------------------------+
| 3 | fcnh9348jmof9348i34f435.png |
+----+-----------------------------+
*/


How can I do that?

Answer

How about using like:

where path like concat('fcnh9348jmof9348i34f435', '.%')

Here is a SQL Fiddle.

EDIT:

If you want to get the part before the first period, then use substring_index():

select substring_index(path, '.', 1)