Mysql query when a condition valids then extract string

Question

I have several fields containing the following contents. I want my query meets all the following fields but with the following conditions:

• First, when there are dot and vipout or vipin in fields just extract the string in between, I mean in case of vipin3601.ym.ms extract 3601 and in case of the vipoutxxp1.ym.ms extract xxp1.

• Sec, in rest of the condition just pick up whatever exists.

   select provider from test group by provider;
      +------------------+
      | provider   |
      +------------------+
      | 360-lay     |
      | 382Com           |
      | e-gus           |
      | e-ggg-inbound   |
      | ttthts           |
      | inefthyther        |
      | jfc              |
      | kefdfoo           |
      | vipin3601.ym.ms  |
      | vipinstm.ym.ms   |
      | vipout382.ym.ms  |
      | vipoutils2.ym.ms |
      | vipoutiot.ym.ms  |
      | vipoutxxp1.ym.ms |
      | vipoutqlm.ym.ms  |
      | vipoutspm.ym.ms  |
      | unknown          |
      +------------------+
      18 rows in set (0.00 sec)
  

I have tried something like:

select  left(provider,locate('.',provider)-1) from test group by provider;

But this returns as below and this is not what I want. I don't know how to change it to have the desired results.

 vipin3601 vipinstm vipout382 vipoutils vipoutiot vipoutxxp vipoutqlm vipoutspm

Thank you for any helps.

Answer 1

You are basically going to need to build out a complex case statement here. That might look like:

SELECT
  CASE
    /* vipin case */
    WHEN provider LIKE 'vipin%.%'
        /* grab substring up to first '.'
         * then get substring of that starting at 6th position
         * to accomodate for 5 letters in 'vipin'
         */
        THEN SUBSTRING(SUBSTRING_INDEX(provider, '.', 1), 6)
    /* vipout case */
    WHEN provider LIKE 'vipout%.%'
        /* grab substring up to first '.'
         * then get substring of that starting at 6th position
         * to accomodate for 6 letters in 'vipout'
         */
        THEN SUBSTRING(SUBSTRING_INDEX(provider, '.', 1), 7)
    /* all other cases */
    ELSE provider
  END AS result
FROM test

Note that I did not include your GROUP BY clause as it does not seem to do anything here, since there are no aggregation functions being used.

Answer 2

Try this:

SELECT CASE 
          WHEN provider LIKE 'vipin%.%' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(provider, '.', 1), 'vipin', -1) 
          WHEN provider LIKE 'vipout%.%' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(provider, '.', 1), 'vipout', -1) 
       END
FROM test
WhERE provider LIKE 'vipin%.%' OR provider LIKE 'vipout%.%'