saperlipopette saperlipopette - 1 month ago 24
MySQL Question

Select distinct first words of a field

i would like to be able to do a select query, taking only the distinct first words of the string, excluding the last one. If not clear, here is the result i'd like to have for the following table :

Apple iPhone 4S

Apple iPhone 4

Apple iPhone 6 Plus


+-----------+--------------------------------+
| id | model |
+-----------+--------------------------------+
| 1 | Apple iPhone 4S 16Gb |
| 2 | Apple iPhone 4S 32Gb |
| 3 | Apple iPhone 4 8Gb |
| 4 | Apple iPhone 6 Plus 32Gb |
| 5 | Apple iPhone 6 Plus 64Gb |
+-----------+--------------------------------+


How to select the distinct values of the field while excluding the values after the last space ?

I tried to find documentation about functions like a sort of
right(model, lastindexof(" ")).
Thank you in advance for your help :)

Answer

You can reverse the string to find the first space " "

SQL Demo

SELECT DISTINCT RTRIM(REVERSE(SUBSTRING(REVERSE(`model`),LOCATE(" ",REVERSE(`model`)))))
FROM Table1

OUTPUT

enter image description here