CODEWITHSUNDEEP
mysqlselect
saperlipopette
saperlipopette

Reputation: 177

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 :)

Upvotes: 2

Views: 1129

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 6

CptMisery
CptMisery

Reputation: 614

SELECT DISTINCT TRIM(SUBSTRING(model, 1, LENGTH(TRIM(model))-4))

Upvotes: 2

Related Questions