0

The following query

SELECT ASSOCIATED_RISK 
FROM PROJECT_ISSUES
 WHERE FIND_IN_SET('98',ASSOCIATED_RISK);

returns output as

96,98

90,98

but if I use 

SELECT ASSOCIATED_RISK
FROM PROJECT_ISSUES 
 WHERE FIND_IN_SET('96,98',ASSOCIATED_RISK);

it doesn't returns anything.In this case I would like to retrieve the first row.

96,98
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
eshaa
  • 386
  • 2
  • 7
  • 26

3 Answers3

2

Use the AND clause, like this:

SELECT ASSOCIATED_RISK 
FROM PROJECT_ISSUES 
WHERE FIND_IN_SET('96',ASSOCIATED_RISK)
AND FIND_IN_SET('98',ASSOCIATED_RISK)

Your query is failing because FIND_IN_SET() does not work properly if the first argument contains a comma (",") character. Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set. In your case, the first argument is '96,98', so it fails.

Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
  • This gives the proper result.However is there any other way I can get it in single query instead of framing multiple find_in_set and concat them ? – eshaa Feb 19 '14 at 11:58
  • @user1650864 sorry, I dont think it is possible unless you can change the table structure. Ideally comma separated values should not be stored in a database column – Aziz Shaikh Feb 19 '14 at 12:01
1

Your comment:

is there any other way I can get it in single query instead of framing multiple find_in_set and concat them

As an alternative solution, you can use locate on your ASSOCIATED_RISK value.

Example: 

locate( replace( '96,98', ',', '' ), replace( ASSOCIATED_RISK, ',', '' ) )

Edit:
As per Aziz Shaikh comment, we can see that there is a possibility of true result though the search string not existing in the target string.

As an alternative solution, you can replace the search string from target string with an empty string and compare the lengths. If original string's length is grater than new replaced string, then it is a found true result.

Example: 

-- this should be greater than 0 for a found true
length( ASSOCIATED_RISK ) > length( replace( ASSOCIATED_RISK, '96,98', '' ) )
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
  • @user1650864 this works but do watch out if `ASSOCIATED_RISK` contains a value which is something like this `'9698,97'`, the above query will also return `'9698,97'` because it is replace the `,` and searching as a string. – Aziz Shaikh Feb 19 '14 at 12:29
  • @AzizShaikh: You are right. Alternative solution is replace the search string with empty in target string and compare the lengths with `>` for *found true*. – Ravinder Reddy Feb 19 '14 at 12:46
  • @user1650864: There is a bug found in my solution suggested. I have updated with an alternative. – Ravinder Reddy Feb 19 '14 at 12:53
  • @Ravinder will the `length` part be used with the `locate` part using an `AND` ? – Aziz Shaikh Feb 19 '14 at 12:55
  • @AzizShaikh: We can, but it depends on the necessity to use. As you already pointed out a possible issue, I suggest not to depend on `locate(replace(...` solution. – Ravinder Reddy Feb 19 '14 at 15:13
  • Some observation on various queries. SELECT ASSOCIATED_RISK FROM PROJECT_ISSUES WHERE LOCATE( REPLACE( '96,98', ',', '' ), REPLACE( ASSOCIATED_RISK, ',', '' ) ); returns one value 96,98 but if i use reverse search SELECT ASSOCIATED_RISK FROM PROJECT_ISSUES WHERE LOCATE( REPLACE( '98,96', ',', '' ), REPLACE( ASSOCIATED_RISK, ',', '' ) ); it doesn't return record. However the below works in either way SELECT * FROM PROJECT_ISSUES WHERE FIND_IN_SET('98',ASSOCIATED_RISK) AND FIND_IN_SET('96',ASSOCIATED_RISK); – eshaa Feb 21 '14 at 12:15
  • 1
    No. `Find_in_set` returns true only if found in sequential order. For reverse order search, it won't work. You need to opt a different approach. – Ravinder Reddy Feb 21 '14 at 13:00
0

This will Give result. see the difference. 

SELECT ASSOCIATED_RISK FROM PROJECT_ISSUES WHERE FIND_IN_SET(ASSOCIATED_RISK,'96,98');
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
Shrikant Gupta
  • 127
  • 1
  • 1
  • 9