The requirement is simple I need to get the rows having exactly '4' in it but the problem is that in Sqllite there is no FIND_IN_SET function.I am trying to get the values using LIKE Query:
要求很简单，我需要得到包含“4”的行，但问题是在Sqllite中没有FIND_IN_SET函数。我正在尝试使用查询来获取值:
The code snippets I have tried
我尝试过的代码片段
select * from tbl_opportunity where (',' || opp_Business_id || ',') LIKE ',4,%'
but it is returning only 2 rows and sometimes also more if the row contains '24,' or any number having data like this '4,'
但它只返回2行，有时还返回更多如果行包含'24 '或任何数据如'4 '
1 个解决方案
#1
2
Sqlite does support regular expressions WHERE opp_Business_id REGEXP "\b" || 4 || "\b"; - otherwise you would need to match for all possible combinations. that your pattern does not match the least should be obvious, when it fails 2/3 of the example records; WHERE (opp_Business_id == '4' OR opp_Business_id LIKE '%,4' OR opp_Business_id LIKE '4,%' OR opp_Business_id LIKE '%,4,%'); that would be four comparisons with LIKE - vs. only one with REGEXP.
Sqlite支持正则表达式，其中opp_Business_id REGEXP“\b”|| 4 ||“\b”;-否则你将需要匹配所有可能的组合。当您的模式不匹配时，应该很明显，当它失败了2/3的示例记录;其中(opp_Business_id = '4'或opp_Business_id LIKE '%，4'或opp_Business_id LIKE '%， %'或opp_Business_id LIKE '%，4，%');这将是四个与LIKE的比较，而只有一个与REGEXP的比较。
#1
2
Sqlite does support regular expressions WHERE opp_Business_id REGEXP "\b" || 4 || "\b"; - otherwise you would need to match for all possible combinations. that your pattern does not match the least should be obvious, when it fails 2/3 of the example records; WHERE (opp_Business_id == '4' OR opp_Business_id LIKE '%,4' OR opp_Business_id LIKE '4,%' OR opp_Business_id LIKE '%,4,%'); that would be four comparisons with LIKE - vs. only one with REGEXP.
Sqlite支持正则表达式，其中opp_Business_id REGEXP“\b”|| 4 ||“\b”;-否则你将需要匹配所有可能的组合。当您的模式不匹配时，应该很明显，当它失败了2/3的示例记录;其中(opp_Business_id = '4'或opp_Business_id LIKE '%，4'或opp_Business_id LIKE '%， %'或opp_Business_id LIKE '%，4，%');这将是四个与LIKE的比较，而只有一个与REGEXP的比较。