SQLite查询含逗号分隔值列中精确匹配'4'的行

Let's sort out this issue where you need to find rows in tbl_opportunity where opp_Business_id exactly contains the value '4' (as a standalone element in a comma-separated string). Your initial LIKE approach has two problems: it either misses valid rows or catches false matches like '24,'. Here's how to fix it properly:

Correct Approach 1: Use Surrounding Commas with Proper Wildcards #

The key is to ensure '4' is treated as a separate element. We can wrap the column value with commas on both ends, then look for the exact pattern ,4, anywhere in that wrapped string. This avoids false matches and covers all positions of '4':

SELECT * FROM tbl_opportunity
WHERE (',' || opp_Business_id || ',') LIKE '%,4,%';

Why this works:

• If opp_Business_id is just '4', wrapping turns it into ,4, which matches %,4,%
• If it's '4,12', wrapping gives ,4,12, which contains ,4,
• If it's '12,4', wrapping gives ,12,4, which contains ,4,
• If it's '12,4,13', wrapping gives ,12,4,13, which contains ,4,
• False matches like '24' become ,24, which doesn't have ,4,, so they're excluded

Correct Approach 2: Use the INSTR Function #

If you prefer a more explicit check, SQLite's INSTR function can look for the ,4, substring directly in the wrapped value:

SELECT * FROM tbl_opportunity
WHERE INSTR(',' || opp_Business_id || ',', ',4,') > 0;

This does the exact same thing as the LIKE query, but some developers find it more readable.

Why Your Original Query Failed #

• When you used (',' || opp_Business_id || ',') LIKE ',4,%', the % only covers characters after ,4,, so it would only match cases where '4' is the first element (like '4' or '4,12'). It misses rows where '4' is in the middle or at the end.
• If you skipped wrapping the column and used something like opp_Business_id LIKE '%4,%', you'd catch false matches like '24,' because the substring '4,' exists within '24,'.

Handling NULL Values (Optional) #

If opp_Business_id can be NULL, add a check to avoid NULL concatenation issues:

SELECT * FROM tbl_opportunity
WHERE opp_Business_id IS NOT NULL
AND (',' || opp_Business_id || ',') LIKE '%,4,%';

内容的提问来源于stack exchange，提问作者androholic