How to determine if the content of two comma-separated strings is completely contained without using custom functions in TiDB

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何判断2个字符串逗号分割内容完全包含的，tidb没有自定义函数使用

| username: TiDBer_BLeyZ3MR

To determine if each element in Field A appears in Field B, you can use the following SQL query:

SELECT 
  CASE 
    WHEN NOT EXISTS (
      SELECT 1 
      FROM (
        SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(a.data, ',', numbers.n), ',', -1)) AS value
        FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers
        CROSS JOIN (SELECT '1,2,3,4' AS data) a
      ) AS A
      LEFT JOIN (
        SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(b.data, ',', numbers.n), ',', -1)) AS value
        FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7) numbers
        CROSS JOIN (SELECT '3,1,2,4,7' AS data) b
      ) AS B
      ON A.value = B.value
      WHERE B.value IS NULL
    ) 
    THEN 'true' 
    ELSE 'false' 
  END AS result;

This query splits the comma-separated values in Field A and Field B into individual rows, then checks if any value in Field A does not exist in Field B. If all values in Field A are found in Field B, it returns ‘true’; otherwise, it returns ‘false’.

2
| username: buddyyuan | Original post link

First, split the first one into 4 elements, then FIND_IN_SET should work.

3
| username: Miracle | Original post link

Using SQL is too complicated; implementing it in code is simpler.

4
| username: tidb菜鸟一只 | Original post link

CREATE TABLE numbers (
num INT
);

– Insert numbers from 0 to 100
INSERT INTO numbers (num)
SELECT n FROM (
SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL
SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL
SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL
SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39 UNION ALL
SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44 UNION ALL
SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49 UNION ALL
SELECT 50 UNION ALL SELECT 51 UNION ALL SELECT 52 UNION ALL SELECT 53 UNION ALL SELECT 54 UNION ALL
SELECT 55 UNION ALL SELECT 56 UNION ALL SELECT 57 UNION ALL SELECT 58 UNION ALL SELECT 59 UNION ALL
SELECT 60 UNION ALL SELECT 61 UNION ALL SELECT 62 UNION ALL SELECT 63 UNION ALL SELECT 64 UNION ALL
SELECT 65 UNION ALL SELECT 66 UNION ALL SELECT 67 UNION ALL SELECT 68 UNION ALL SELECT 69 UNION ALL
SELECT 70 UNION ALL SELECT 71 UNION ALL SELECT 72 UNION ALL SELECT 73 UNION ALL SELECT 74 UNION ALL
SELECT 75 UNION ALL SELECT 76 UNION ALL SELECT 77 UNION ALL SELECT 78 UNION ALL SELECT 79 UNION ALL
SELECT 80 UNION ALL SELECT 81 UNION ALL SELECT 82 UNION ALL SELECT 83 UNION ALL SELECT 84 UNION ALL
SELECT 85 UNION ALL SELECT 86 UNION ALL SELECT 87 UNION ALL SELECT 88 UNION ALL SELECT 89 UNION ALL
SELECT 90 UNION ALL SELECT 91 UNION ALL SELECT 92 UNION ALL SELECT 93 UNION ALL SELECT 94 UNION ALL
SELECT 95 UNION ALL SELECT 96 UNION ALL SELECT 97 UNION ALL SELECT 98 UNION ALL SELECT 99 UNION ALL
SELECT 100
) AS nums;

– Query the numbers table to verify if the number sequence was generated successfully
SELECT * FROM numbers;

CREATE TABLE test_data (
A_id INT,
A_data VARCHAR(100),
B_id INT,
B_data VARCHAR(100)
);

– Insert data
INSERT INTO test_data (A_id, A_data, B_id, B_data) VALUES
(1, ‘1,2,3,4’, 1, ‘3,1,2,4,7’),
(2, ‘5,6,7’, 2, ‘6,8,9,10’);

WITH A_elements AS (
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(A_data, ‘,’, num), ‘,’, -1) AS element,
A_id
FROM test_data
JOIN numbers
ON num <= LENGTH(A_data) - LENGTH(REPLACE(A_data, ‘,’, ‘’)) + 1
),
– Split field B into individual rows
B_elements AS (
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(B_data, ‘,’, num), ‘,’, -1) AS element,
B_id
FROM test_data
JOIN numbers
ON num <= LENGTH(B_data) - LENGTH(REPLACE(B_data, ‘,’, ‘’)) + 1
)
– Check if each element in A appears in B
SELECT
A_id,
CASE WHEN COUNT(B_elements.element) = COUNT(A_elements.element) THEN ‘true’ ELSE ‘false’ END AS result
FROM A_elements
LEFT JOIN B_elements ON A_elements.element = B_elements.element
GROUP BY A_id
ORDER BY 1;
Something like this

5
| username: GreenGuan | Original post link

Does it have to be done in the database? It seems more convenient to do sorting + md5 comparison in the code.

6
| username: DBAER | Original post link

Logical issues should be handled in the code, and the database should only be used for reading and writing.

7
| username: TiDBer_xV0VbTdz | Original post link

Master, the description has been changed.

8
| username: TiDBer_xV0VbTdz | Original post link

Our business is migrating to TiDB for processing. Previously, we used a self-written function, and the migration is to solve speed issues.

9
| username: TiDBer_xV0VbTdz | Original post link

Our business has migrated to TiDB for processing. Previously, we used a self-written function, and the migration was to solve speed issues.

10
| username: Kongdom | Original post link

:thinking: It seems that there is no good solution. We usually parse and concatenate SQL at the application layer.

11
| username: zhang_2023 | Original post link

Code solutions are better.

12
| username: 霸王龙的日常 | Original post link

Use code to implement it, TiDB does not support custom functions.

13
| username: 小于同学 | Original post link

TiDB’s judgment is too complicated, isn’t it?

14
| username: RenlySir | Original post link

Let’s implement the code~

15
| username: TiDBer_ok0VXN1s | Original post link

Suggest code implementation

16
| username: Hacker_QGgM2nks | Original post link

TiDB does not support it. It seems to be in progress. Just wait, or handle it with code.

17
| username: changpeng75 | Original post link

For this kind of requirement, it is better to implement it in the front-end code. Even in traditional relational databases, even with functions and stored procedures, the efficiency of implementing these logics on the database side is not high.

18
| username: 健康的腰间盘 | Original post link

SQL has its limits.

19
| username: QH琉璃 | Original post link

Let’s learn together.

20
| username: zhanggame1 | Original post link

This difficulty is not yet the limit of SQL.