The partial function replacement scheme of Damin database and mysql database

Find in set in mysql database

FIND_IN_SET(str,strlist)

str string to query
strlist field name parameters are separated by "," (1,2,6,8)
Query the result of (str) contained in the field (strlist), return the result as null or record

If the string str is in the string list strlist composed of N sub chains, the return value range is between 1 and n. A list of strings is a string composed of several sub chains separated by the ',' symbols. If the first parameter is a constant string, and the second is the type SET column, the find in set() function is optimized to use bit calculation. If str is not in strlist or strlist is an empty string, the return value is 0. If any parameter is NULL, the return value is NULL. This function will not function properly when the first argument contains a comma (',')

mysql> SELECT FIND_IN_SET('b', 'a,b,c,d'); 
-> 2 because b stay strlist Place 2 in the collection starting from 1
select FIND_IN_SET('1', '1'); It's time to go back strlist Collection is a bit special. Only one string requires that the previous string must return a number greater than 0 in the latter string collection 
select FIND_IN_SET('2', '1,2'); Return to 2 
select FIND_IN_SET('6', '1'); Return to 0

There is no function with the same function in Daydream database. You need to customize the find in set function, which is as follows:

create or replace FUNCTION FIND_IN_SET(piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ',')  
    RETURN NUMBER IS  
      l_idx    number:=0; -- Used for calculation piv_str2 Location of separator in  
      str      varchar2(500);  -- Substring intercepted by separator  
      piv_str  varchar2(500) := piv_str2; -- take piv_str2 Assign to piv_str  
      res      number:=0; -- Return result  
      res_place      number:=0;-- The position of the original string in the target string  
    BEGIN  
    -- If the field is null Then return to 0  
    IF piv_str2 IS NULL THEN  
      RETURN res;  
    END IF;  
    -- If piv_str No separator in, direct judgment piv_str1 and piv_str Equal or not res_place=1  
    IF instr(piv_str, p_sep, 1) = 0 THEN  
       IF piv_str = piv_str1 THEN  
          res_place:=1;  
          res:= res_place;  
       END IF;  
    ELSE  
     -- Loop by separator piv_str  
    LOOP  
        l_idx := instr(piv_str,p_sep);  
        --  
        res_place := res_place + 1;  
        -- When piv_str When there is a separator in  
          IF l_idx > 0 THEN  
          -- Field before first separator str  
             str:= substr(piv_str,1,l_idx-1);  
             -- judge str and piv_str1 If it is equal, it will end the cycle judgment  
             IF str = piv_str1 THEN  
               res:= res_place;  
               EXIT;  
             END IF;  
            piv_str := substr(piv_str,l_idx+length(p_sep));  
          ELSE  
          -- When intercepted piv_str If there is no separator in, judge piv_str and piv_str1 Equal or not res=res_path  
            IF piv_str = piv_str1 THEN  
               res:= res_place;  
            END IF;  
            -- Jump out of the loop whether it's equal or not  
            EXIT;  
          END IF;  
     END LOOP;  
     -- End cycle  
     END IF;  
     -- Return res  
     RETURN res;  
    END FIND_IN_SET;
Group concat function in mysql database

In a query statement with group by, the fields specified by select are either included after the group by statement as the basis for grouping, or included in the aggregate function.
1. Function: connect the values in the same group generated by group by and return a string result.

2. Syntax: Group concat ([distinct] field to connect [order by sort field ASC / desc] [separator 'separator'])

Note: duplicate values can be excluded by using distinct; if you want to sort the values in the results, you can use the order by clause; the separator is a string value, and the default is a comma.

WM ﹣ concat, a function with similar functions, is used to realize this function in Daydream database

insert into TFun06 (ID, VAL, NUM) values (10, 'abc', 1);
insert into TFun06 (ID, VAL, NUM) values (10, 'abc', 2);
insert into TFun06 (ID, VAL, NUM) values (10, 'def', 3);
insert into TFun06 (ID, VAL, NUM) values (10, 'def', 4);

insert into TFun06 (ID, VAL, NUM) values (11, 'abc', 1);
insert into TFun06 (ID, VAL, NUM) values (11, 'abc', 2);
insert into TFun06 (ID, VAL, NUM) values (11, 'def', 3);
insert into TFun06 (ID, VAL, NUM) values (11, 'def', 4);

SELECT ID, regexp_replace(WM_CONCAT(DISTINCT VAL),',',';') AS ENAMES FROM TFun06 GROUP BY ID ORDER BY ID; 

ID  ENAMES
10	def;abc	
11	abc;def	
Published 10 original articles, praised 0, visited 269
Private letter follow

Tags: Database MySQL

Posted on Tue, 10 Mar 2020 05:29:37 -0400 by deepson2