Get two columns with if condition mysql

Question

I have below table structure:

cv1    cv2   cv_content1   cv_content2
abc    xyz     php,mysql     sql dotnet
aaa    xxx     java          php is my skill

I have two keywords lets say php and dotnet. Now I want to fetch record from above table t1 with two previous mentioned keywords in column cv_content1 and cv_content2 with OR condition. I want below records:

cv1   cv2
abc    xyz  
---    xxx

I tried case statement and if also and I also have to check that how many keywords match for a record for example for first row of result it is 100%(php and dotnet both are in row) but for second row it is 50%(only php matched).

I tried below query so far:

    SELECT ROUND(( ( if(LOCATE("php",cv_content1 )>0,25,0) OR if(LOCATE("php",cv_content2 )>0,25,0) ) + if(LOCATE("dotnet",cv_content1 )>0,25,0) OR if(LOCATE("dotnet",cv_content2 )>0,25,0) ) * 100 ) / 50) as SkillTot,if (own_content regexp '[[:<:]]smith[[:>:]]',`own_filename`),if (kings_content regexp '[[:<:]]smith[[:>:]]' ,`kings_filename`) FROM `t1`WHERE ( cv_content1 REGEXP '[[:<:]]php[[:>:]]' OR cv_content2 REGEXP '[[:<:]]php[[:>:]]' OR cv_content1 REGEXP '[[:<:]]dotnet[[:>:]]' OR cv_content2 REGEXP '[[:<:]]dotnet[[:>:]]')

It has a syntax error also. Don't understand how to use if statement

The way you're storing your keywords is bad. You'll face problems querying for data — asprin, May 20 '14 at 06:21
Are cv_content1 and cv_content2 strings? And all the tags are just comma separated string? If that is the case you should change your database layout — WizKid, May 20 '14 at 06:21
cv_contet1 and cv_contetn2 are long text and i am saving cv data in text format here — Bharat Maheshwari, May 20 '14 at 06:22
See this Q&A http://stackoverflow.com/q/4155873/ plus using either `IN` or `FIND_IN_SET` will be useful. — Funk Forty Niner, May 20 '14 at 06:25
I edit my question with more information. Please check if it can explain what i want — Bharat Maheshwari, May 20 '14 at 06:46
The added new `regex` form of query should be in a new post. — Ravinder Reddy, May 20 '14 at 06:50

score 1 · Accepted Answer · answered May 20 '14 at 06:42

SELECT IF(FIND_IN_SET('php', cv_content1) OR FIND_IN_SET('dotnet', cv_content1),
          cv1, NULL) AS cv1,
       IF(FIND_IN_SET('php', cv_content2) OR FIND_IN_SET('dotnet', cv_content2),
          cv2, NULL) AS cv2
FROM YourTable
HAVING cv1 IS NOT NULL OR cv2 IS NOT NULL

Ravinder Reddy · Answer 2 · 2014-05-20T07:02:51.510

0

Use find_in_set to search and OR to find any of the input values.

Example:

select cv1, cv2
  from t1
 where find_in_set( 'php', concat( cv_content1, ',', cv_content2 ) ) > 0 
    or find_in_set( 'dotnet', concat( cv_content1, ',', cv_content2 ) ) > 0

Edit 1:

select 
   case when find_in_set( 'php', cv_content1 ) 
          or find_in_set( 'dotnet', cv_content1 ) 
        then cv1 else '---' end as cv1
 , case when find_in_set( 'php', cv_content2 ) 
          or find_in_set( 'dotnet', cv_content2 ) 
        then cv2 else '---' end as cv2
from t1;

Demo @ MySQL Fiddle

Edit 2:

select 
   case when find_in_set( 'php', replace( cv_content1, ' ', ',' ) ) 
          or find_in_set( 'dotnet', replace( cv_content1, ' ', ',' ) ) 
        then cv1 else '---' end as cv1
 , case when find_in_set( 'php', replace( cv_content2, ' ', ',' ) ) 
          or find_in_set( 'dotnet', replace( cv_content2, ' ', ',' ) ) 
        then cv2 else '---' end as cv2
from t1;

Demo @ MySQL Fiddle

edited May 20 '14 at 07:02

answered May 20 '14 at 06:24

Ravinder Reddy

23,692
6
52
82

it will fetch both cv1 and cv2 values for keyword php and i want if keyword is not in cv_content1 then cv1 should be null – Bharat Maheshwari May 20 '14 at 06:28
Yes. I just saw that you are not expecting `aaa`. Let me re-frame the query. – Ravinder Reddy May 20 '14 at 06:30
@BharatMaheshwari: You changed a bit of your column values format. Check my updated answer again. – Ravinder Reddy May 20 '14 at 07:03

Get two columns with if condition mysql

2 Answers2

Linked