Search code examples
mysqlsql-like

How to return similar values obtained through LIKE operator as one value?

This is a test mysql query that I'm using within a much larger query to filter out the operating systems from my table.

select distinct ostype from prus where ostype LIKE '%%android%%' OR ostype LIKE '%%ios%%' OR ostype LIKE '%%iOS%%';

  ostype
-----------------
 iOS
 generic_ios
 generic_android
 9_android
 M_android
 android

This query returns all kinds android and iOS versions stored.

Now I want to group together and have all android versions (generic_android,9_android,M_android, android) returned/displayed as one single name - Android and same for iOS (iOS, generic_ios) as iOS.

How can this be done?

Solution 
  • select distinct case when ostype LIKE '%%android%%' then 'Android'
                     when ostype LIKE '%%ios%%' then 'IOS'
                     else ostype 
                end as filtered_ostype
from prus