Use regular expression functions in oracle to process comma-separated Fields

In oracle, the question of processing comma-separated fields using regular expression functions is really not easy to write. Its actual meaning is based on a scenario: www.2cto.com, for example, in an oracle table, there are two fields A1 B1 1 A, B, C, D, 2 E, F. If the current requirement is to count the number of B1, for example, when A1 = 1, there are four fields in B1 separated by commas. Therefore, if the number is 4 and A1 = 2, there are two fields E and F, in ORACLE, if the number of all functions in ORACLE is 2, this effect can be achieved. The core is to split the comma-separated characters one by one, the preceding example shows how to change to www.2cto.com A1 B1 1 A 1 B 1 C 1 D 2 E 2 F. The reader may think of it immediately, this is actually A one-to-multiple relational database storage method and is also the most commonly used, but sometimes, A, B, C, and D can also be used to store one-to-many relationships, conversion between the two types In other words, there are two methods for implementation in oracle. 1) The REGEXP_SUBSTR function of the Regular Expression in oracle: the first of the five parameters is the input string, the second is the regular expression, and the third is the identification starting from the first character. (1 by default) The fourth is to identify the matching groups. (1 by default) The fifth is the value range: I: Case Insensitive; c: Case Sensitive; n: point number. do not match line feed characters; m: multi-line mode; x: Extended Mode. Ignore the blank characters in the regular expression. SQL> select * from test_reg_substr; A ----------------------------------------- ABC123XYZ ABC123XYZ456 <Name> Edward </Name> retrieves the intermediate numeric SQL> SELECT 2 REGEXP_SUBSTR (, '[0-9] +') 3 FROM 4 test_reg_substr 5 WHERE 6 REGEXP_LIKE (a, '[0-9] +'); REGEXP_SUBSTR (, '[0-9] +') ------------------------------- 123 search for the intermediate number (match from the first letter to find 123 matching items) SQL> SELECT 2 NVL (REGEXP_SUBSTR (, '[0-9] +', 1, 2), '-') AS a 3 FROM 4 test_reg_substr 5 WHERE 6 REGEXP_LIKE (, '[0-9] +'); A -----------------------------------------------------456 get "Character Set combination" SQL> SELECT 2 REGEXP_SUBSTR (a, '\ w + ') 3 FROM 4 test_reg_substr 5 WHERE 6 REGEXP_LIKE (a, '\ w +'); REGEXP_SUBSTR (A, '\ W + ') ------------------------------- ABC123XYZ ABC123XYZ456 Name get "Character Set combination" (match from the first letter, find 2nd matching items) SQL> SELECT 2 NVL (REGEXP_SUBSTR (a, '\ w + ', 1, 2), '-') AS a 3 FROM 4 test_reg_substr 5 WHERE 6 REGEXP_LIKE (a, '\ w + '); A -----------------------------------------------------description of this topic for more information, see
Http://www.bkjia.com/database/201210/164205.html
The summary in this article is a good summary. Note that only 10 Gb or above is supported. method 1) use the regular expression above. The statement is the Java code SELECT distinct A1, REGEXP_SUBSTR (B1, '[^,] + ', 1, LEVEL) AS A2 from test connect by level <= LENGTH (B1)-LENGTH (REPLACE (B1, ',') + 1) method 2) select a1, substr (b1, 0, instr (b1, ',') from test union select a1, substr (b1, instr (b1, ',') + 1, length (b1) from test method 3 use SQL. First, add a comma after the b1 field. For example, if you want the B1 field in a record to be 'duan, shao, chu, tian 'www.2cto.com SELECT L_COUNT, SUBSTR ('duan, shao, chu, tiany' | ',', INSTR ('duan, shao, chu, tiany' | ',', ',', L_COUNT-1, DECODE (L_COUNT-1, 0, 1, L_COUNT-1) + 1, INSTR ('duan, shao, chu, tian '|', ', 1, DECODE (L_COUNT-1, 0, 1, L_COUNT-1) + 1)-INSTR ('duan, shao, chu, tian '|', ', 1, DECODE (L_COUNT-1, 0, 1, L_COUNT-1)-1) as rs from dual, (select level L_COUNT from dual connect by level <LENGTH ('duan, shao, chu, tiany' | ',')-LENGTH (REPLACE ('duan, shao, chu, tian '|', ') + 1). Methods 2 and 3 can also be used for oracle 9i.