github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_string_findinset.test (about)

     1  #SELECT, 嵌套，中文
     2  SELECT FIND_IN_SET('b','a,b,c,d');
     3  select bin(concat_ws(" ",find_in_set('b','a,b,c,d')));
     4  select find_in_set("b","a,b,c"),find_in_set("c","a,b,c"),find_in_set("dd","a,bbb,dd"),find_in_set("bbb","a,bbb,dd");
     5  select find_in_set("d","a,b,c"),find_in_set("dd","a,bbb,d"),find_in_set("bb","a,bbb,dd");
     6  select find_in_set("","a,b,c"),find_in_set("","a,b,c,"),find_in_set("",",a,b,c");
     7  select find_in_set("abc","abc"),find_in_set("ab","abc"),find_in_set("abcd","abc");
     8  select find_in_set('1','3,1,');
     9  
    10  select find_in_set('你好', "你好,我好,大家好");
    11  
    12  
    13  
    14  #NULL
    15  select find_in_set(Null, null);
    16  select find_in_set('1', null);
    17  select find_in_set(null,"a,b,c,d");
    18  
    19  
    20  #EXTREME VALUES
    21  select find_in_set('a,', 'a,b,c,d');
    22  select find_in_set("*#(()@*31()@*)#)_", "qwkrjqjiofj,*#(()@*31()@*)#)_,f023jf09j2");
    23  select find_in_set('a', 'abcd');
    24  
    25  
    26  #WHERE
    27  create table t1 (a varchar(255));
    28  insert into t1 values('1'),('-1'),('0'),("abc");
    29  select * from t1 where find_in_set('-1', a);
    30  drop table t1;
    31  
    32  
    33  
    34  
    35  #ON, 比较操作
    36  CREATE TABLE t1 (
    37  access_id int NOT NULL default 0,
    38  name varchar(20) default NULL,
    39  `rank` int NOT NULL default 0,
    40  PRIMARY KEY idx (access_id)
    41  );
    42  CREATE TABLE t2 (
    43  faq_group_id int NOT NULL default 0,
    44  faq_id int NOT NULL default 0,
    45  access_id int default NULL,
    46  PRIMARY KEY idx1 (faq_id)
    47  );
    48  INSERT INTO t1 VALUES (1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
    49  INSERT INTO t2 VALUES (261,265,1),(490,494,1);
    50  SELECT t2.faq_id FROM t1 INNER JOIN t2 ON (t1.access_id = t2.access_id) LEFT JOIN t2 t ON (t.faq_group_id = t2.faq_group_id AND find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) WHERE t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
    51  drop table t1;
    52  drop table t2;
    53  
    54  
    55  
    56  #INSERT,distinct
    57  CREATE TABLE t1(a char(255), b int);
    58  INSERT INTO t1 select 'a,b,c,d', FIND_IN_SET('b','a,b,c,d');
    59  INSERT INTO t1 select "a,bbb,dd", find_in_set("dd","a,bbb,dd");
    60  INSERT INTO t1 select "a,b,c", find_in_set("c","a,b,c");
    61  SELECT distinct find_in_set('a', a) FROM t1;
    62  drop table t1;
    63  
    64  #HAVING, 比较运算
    65  CREATE TABLE t1 (a varchar(10));
    66  INSERT INTO t1 VALUES ('abc'), ('xyz');
    67  SELECT a, CONCAT_WS(",",a,' ',a) AS c FROM t1
    68  HAVING find_in_set('a', c) =0;
    69  DROP TABLE t1;
    70  
    71  
    72  
    73  #算式操作
    74  select find_in_set("","a,b,c")*find_in_set("","a,b,c,")-find_in_set("bb","a,bbb,dd");
    75  
    76  #WITH RECURSIVE AS 0.5暂不支持
    77  #create table nodes(id int);
    78  #create table arcs(from_id int, to_id int);
    79  #insert into nodes values(1),(2),(3),(4),(5),(6),(7),(8);
    80  #insert into arcs values(1,3), (3,6), (1,4), (4,6), (6,2), (2,1);
    81  #with recursive cte as
    82  #(
    83  #select id, cast(id as char(200)) as path, 0 as is_cycle
    84  #from nodes where id=1
    85  #union all
    86  #select to_id, concat(cte.path, ",", to_id), find_in_set(to_id, path)
    87  #from arcs, cte
    88  #where from_id=cte.id and is_cycle=0
    89  #)
    90  #select * from cte;
    91  #drop table nodes, arcs;
    92  
    93