CODEWITHSUNDEEP
mysqlselect
saperlipopette
saperlipopette

Reputation: 177

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

Upvotes: 0

Views: 907

Answers (2)

jai dutt
jai dutt

Reputation: 790

Hi This is the perfect solution for you

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

Upvotes: 2

Imanez
Imanez

Reputation: 514

If I understand correctly :

SELECT * FROM  `model` WHERE  `value_model` LIKE  '%Apple iPhone 6%' and `value_model` NOT LIKE  '%Apple iPhone 6 Plus%'

Upvotes: 0

Related Questions