saperlipopette saperlipopette - 1 month ago 5
MySQL Question

Select with the %LIKE% clause and excluding results containing certain words

I've recently made this thread to manage iPhone models in a database to be able to select all the different models by excluding the last word (which is the capacity).

Select distinct first words of a field

+-----------+--------------------------------+
| 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 |
| 6 | Apple iPhone 6 16Gb |
+-----------+--------------------------------+


For this table, i had the following output :

Apple iPhone 4S

Apple iPhone 4

Apple iPhone 6 Plus

Apple iPhone 6


Now, i'd like to be able, for each model, to get the available capacities.
I was thinking about using the LIKE %% clause,

SELECT * FROM `model` WHERE `value_model` LIKE '%Apple iPhone 6S%'


It was working until the models with a 'Plus' appeared.
for the iPhone 4S for exemple i just had to do the following :

SELECT * FROM `model` WHERE `value_model` LIKE '%Apple iPhone 4S%'


I had this input :

Apple iPhone 4S 16Gb

Apple iPhone 4S 32Gb


But for the iPhone 6, it would also include the iPhone 6 Plus in my results.

How could i come across this, by excluding the results with a 'Plus', but also be able to not exclude this 'Plus' when i'm asking for a 'Plus' model.

Thank you in advance for any help :-)

Answer

Hi This is the perfect solution for you

SELECT * FROM  `model`
WHERE TRIM(REPLACE(NAME,SUBSTRING_INDEX(value_model,' ',-1),'')) LIKE  '%Apple iPhone 6'