3

I have SELECT field(foo,'this','that','bar','baz') FROM t WHERE f1 IN ('this','that','bar','baz') How can I avoid listing ('this','that','bar','baz') twice?

Improve this question

1 Answer 1

Reset to default
2

Try using the FIELD function's return value and make sure it is not zero:

SELECT * FROM
(SELECT *,field(foo,'this','that','bar','baz') fieldfoo FROM t) A
WHERE fieldfoo > 0;
Improve this answer
3
  • OK that avoids the strings but still requires writing out N "things" for N strings.
    – chx
    Apr 16, 2012 at 6:40
  • Updated my answer
    – RolandoMySQLDBA
    Apr 16, 2012 at 15:56
  • Ah yes! FIELD is 1-indexed and returns 0 for anything not found, indeed. Nice trick.
    – chx
    Apr 16, 2012 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.