Alternative to FIND_IN_SET in SQLite?

Question

In Mysql, FIND_IN_SET is used to find value in a set. I have tried FIND_IN_SET in SQLite, but it is not an SQL keyword. I have Googled, but I did not get an answer. If anybody knows, please tell me the alternative to FIND_IN_SET in SQLite.

Answer 1

If you need just a true / false value rather than index then you can use LIKE clause:

(',' || column_name || ',') LIKE '%,value,%'

Answer 2

This is my old query

String query = "SELECT a.content_id,a.content_name,a.image_name,a.image_path,a.rating,d.content_type_name"
                + "from content_master a, category_content_mapping b, "
                + "game_detail c, content_type_master d "
                    + "where a.content_id=b.content_id "
                + "and c.content_id = a.content_id "
                + "and a.content_type_id = d.content_type_id "
                + "and b.category_id = '" + category_id + "' "
                + "and find_in_set('" + group_master_id + "',c.group_master_id) "
                + "and a.is_active='Y' and b.is_active = 'Y' and c.is_active = 'Y'"
                + "order by b.content_mapping_id DESC limit 0,3";

Criteria in hibernate use like alternate of find_in_set

    Session session=new Configuration().configure().buildSessionFactory().openSession();
    Criteria criteria=session.createCriteria(ContentMaster.class);
     criteria.setFetchMode("CategoryContentMapping", FetchMode.JOIN);
    criteria.setFetchMode("GameDetail", FetchMode.JOIN);
    criteria.createAlias("categoryContentMappings","cat");
     criteria.createAlias("contentTypeMaster", "ctm");
    criteria.createAlias("gameDetails","game");
    criteria.add(Restrictions.eq("cat.categoryMaster.categoryMasterId", 9));

    criteria.add(Restrictions.disjunction()
    .add(Restrictions.like("game.groupMasterId","%12,%"))
    .add(Restrictions.like("game.groupMasterId","%,12,%"))
    .add(Restrictions.like("game.groupMasterId","%12%")));
    criteria.add(Restrictions.eq("isActive", "y"));
    criteria.add(Restrictions.eq("cat.isActive", "y"));
    criteria.add(Restrictions.eq("ctm.isActive", "y"));
    criteria.addOrder(Order.desc("cat.contentMappingId"));

Answer 3

we can write query like change into hibernate critearea

my old query

select * FROM game_detail WHERE content_id=176 and FIND_IN_SET(12,group_master_id)

New query

select * 
  FROM game_detail
 WHERE content_id=176
   and (group_master_id LIKE '%12,%'|| group_master_id LIKE '%,12,'|| group_master_id LIKE '%12')