CODEWITHSUNDEEP
mysql
Aravind S
Aravind S

Reputation: 535

MySql : Display value in a specific column based on a condition

I'm having a scenario where I need to display a value into a particular column based on a condition. In detail, say, I have a value which I got from some column whose value contains the string "a beautiful day". And I have four columns; columnA, columnB, columnC, columnD wherein I need to display this value in only one of the four columns based on a condition.

Say if this value "a beautiful day" contains equal or than more 3'a's then I need this to be displayed in columnA and the rest of columns should be empty. And if it contains 2 'a's, I need this to be displayed in the columnB and similary if 1 'a' columnC and if no 'a's, columnD.

It should be something like : 

+-------------------+-------------------+-----------+-----------+-----------+
| stringValue       | columnA           | columnB   | columnC   | columnD   |
+-------------------+-------------------+-----------+-----------+-----------+
| a beautiful day   |a beautiful day    |           |           |           |
| Chelsea F C       |                   |           |Chelsea F C|           |
+-------------------+-------------------+-----------+-----------+-----------+

Can this be made possible in MySql? if possible, please explain me how.

Thanks in advance.

Upvotes: 1

Views: 566

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could check for length before and after replace of 'a' with '' 

select case when (length(stringValue ) - length(replace(stringValue, 'a',''))) >= 3 
           then stringValue else null end columnA
    ,  case when (length(stringValue ) - length(replace(stringValue, 'a','')))= 2 
           then stringValue else null end columnB
    , case when (length(stringValue ) - length(replace(stringValue, 'a',''))) = 1 
           then stringValue else null end columnC
    , case when (length(stringValue ) - length(replace(stringValue, 'a',''))) = 0
           then stringValue else null end columnD
FROM my_table

Upvotes: 1

Related Questions