Alternate to FIND_IN_SET for non-MySQL databases
If you are MySQL user initially at the beginning of your career and later on as the career progresses got in touch with other RDBMS then you might miss MySQL's FIND_IN_SET function badly. 🙂
What MySQL's FIND_IN_SET does is it returns position of the matched element in the SET. That SET of items must be comma-separated.
For example take following code into consideration:
It will return "2" as the result. So generally what we have been doing is we use it not to find whether particular element is at what position in the comma-separated list but just to find simply that whether item exists in the list at all or not?
For that we use SQL something like following:
SELECT COUNT(0) FROM products WHERE FIND_IN_SET('7', category_id);
Above query will give you count of records which are having category_id 7 along with or without other categories. In table records might have values comma-separated like '4,5,7' or '7' or '2,7,8' or anything. So above expression in WHERE will return value greater than "zero" (0) and that row will be returned in result or calculated in count. Here category_id in products table is of type varchar.
Now when we are not using MySQL many times we can get similar result if we are not worried about the position of the item in the set but just want to make sure that it exists or not. It can be achieved with following SQL statement.
/** For non-mysql databases, an alternate to FIND_IN_SET **/ SELECT COUNT(0) FROM PRODUCTS WHERE ','+category_id+',' LIKE '%,7,%';
Now in above condition while storing list of values using comma is not required it can be anything you want, while comma is just natural choice.
Same can be used with MySQL also, here is the MySQL variant for the above alternate.
SELECT COUNT(0) FROM PRODUCTS WHERE CONCAT(',', category_id, ',') LIKE '%,7,%';
What did you used when you ever came across some other MySQL specific functions alternates or may be for the same FIND_IN_SET? Share your story with us and if you like share this post with your friends.!