SQL case study: merging and splitting strings

This article brings you relevant knowledge about SQL server. It mainly introduces you to the relevant information about the merging and splitting of strings in SQL case study. The article introduces the two methods respectively. This method has certain reference learning value for everyone to learn or use Oracle. Friends in need can refer to it.

Recommended study: "SQL Tutorial"

Merge of strings

There may be multiple implementation methods in Oracle. Currently, there are two known to me. The implementation of these two types is recorded below:

String merging method One:

Implement SQL:

--方法一
SELECT d.dept_name,wm_concat(e.emp_name) FROM employee e
INNER JOIN department d ON d.dept_id=e.dept_id
GROUP BY d.dept_name;

Execution result:

SQL analysis:

Use Oracle's own wm_concat() function to merge strings. There is a disadvantage here. The merged connection symbol can only be the default comma, and other symbols cannot be used.

String merging method two:

Implement SQL:

--方法二
SELECT d.dept_name,
LISTAGG (e.emp_name, ',') WITHIN GROUP (ORDER BY e.emp_name) names
FROM employee e
INNER JOIN department d ON d.dept_id=e.dept_id
GROUP BY d.dept_name;

Execution result:

SQL analysis:

Use Oracle's own LISTAGG() function to merge strings. Its advantage is that the merged connection symbol can be specified as any characters, and can easily implement ORDER BY sorting.

String splitting

There may be multiple implementation methods in Oracle, the ones I know so far There are two types. The implementation of these two types is recorded below:

String splitting method one:

Implementation of SQL:

--方法一
WITH  t (id, name, sub, str) AS (
    SELECT id, name, substr(class, 1, instr(class, '、')-1), substr(concat(class,'、'), instr(class, '、')+1) 
    FROM movies
    
    UNION ALL
    
    SELECT id, name,substr(str, 1, instr(str, '、')-1), substr(str, instr(str, '、')+1)
    FROM t WHERE instr(str, '、')>0
) 
 
SELECT id, name, sub
FROM t
ORDER BY id;

Execution result:

##SQL analysis:

This statement is a little complicated, and it will be explained step by step below:

First look at the original data of the movies table:

1. The first step is to divide the value of the class field according to the delimiter (here is a comma) Perform a preliminary split into two parts. The first part is the first value of the class field to be split, and the second part is the value of the remaining parts of the class field to be split.

2. The second step uses WITH expression to implement recursive query, and loops the unsplit values ​​in the first step according to the delimiter (here is the comma) (Part 2) Split until the last delimiter of the field, and the data at the end of the recursion is placed in the temporary table t.

3. The third step is a simple query to query and sort the records from the temporary table t in the second step.

String splitting method two:

Implement SQL:

--方法二
SELECT m.name,t.column_value FROM movies m,TABLE(SPLIT(m.class,'、')) t;

Execution result:

SQL analysis:

This method actually processes strings by customizing a function. , the logic of function split is actually similar to the logic of method 1. They both use recursion to split the values ​​in the string one by one according to the delimiter, and finally return the split string. Personally, I feel this method is better because it encapsulates the splitting logic, making it simpler to use and clearer in logic.

The following is the creation script of the split function:

create or replace function split (p_list clob, p_sep varchar2 := ',')
  return tabletype
  pipelined
 
is
  l_idx    pls_integer;
  v_list  varchar2 (32676) := to_char(p_list);
begin
  loop
      l_idx  := instr (v_list, p_sep);
 
      if l_idx > 0
      then
        pipe row (substr (v_list, 1, l_idx - 1));
        v_list  := substr (v_list, l_idx + length (p_sep));
      else
        pipe row (v_list);
        exit;
      end if;
  end loop;
end;

The return value type of the function, tabletype, is also a custom type.

The following is the creation script of this type:

create or replace type tabletype as table of varchar2(32676);

Recommended learning: "

SQL Tutorial"

The above is the detailed content of SQL case study: merging and splitting strings. For more information, please follow other related articles on the PHP Chinese website!