Search code examples
mysqlsqlsql-serverreplaceqsqlquery

Query in SQL - 7 Boom

I need to write a query that represents the following:

I have 1 Table "Numbers" with one column only "Num" (Integer). The table includes data from 1 to N.

I need to write a query, that replaces the word "Boom" each time the number 7 shows up or each time we have a number which can be divided by 7. So at the end I get the following: 1 2 3 4 5 6 Boom 8 9 10 11 12 13 Boom 15 16 Boom 18 19 20 Boom ....

Hope it's clear :)

Thank you

Solution

  • You can use CASE. For example:

    select
  case when n % 7 = 0 or concat('x', n) like '%7%' then 'Boom'
       else concat('', n)
  end
from numbers

    Result:

     Result 
 ------ 
 1      
 2      
 3      
 4      
 5      
 6      
 Boom   
 8      
 9      
 10     
 11     
 12     
 13     
 Boom   
 15     
 16     
 Boom   
 18     
 19     
 20     
 Boom   
 22     
 23     
 24     
 25     
 26     
 Boom   
 Boom   
 29     
 30

    See running example at DB Fiddle.