How to use SQL to convert a string separated by commas into a list (go)

How to convert a string separated by commas into a list, as an example of a comma delimiter:

For example, there is a string whose value is: Hong Kong, Zhangjiagang, Beijing, Shanghai
The way to convert this string into a list with SQL is:

1, Method one

 withA as(SELECT 'Hong Kong, Zhangjiagang, Beijing, Shanghai'A fromDUAL)SELECTDECODE (B,0, SUBSTR (A,c), SUBSTR (a,c,b-C) City from(SELECTA, A, (LAG (B,1,0) Over(ORDER  byLV))+1C from(SELECTA,instr (A,',',1, LevelB LevelLv fromAconnect by  Level <=(LENGTH (A)-LENGTH (REPLACEA',',"')))+1))

The output is:

Hong Kong Zhangjiagang Beijing Shanghai

Application Examples:
If the value of the City field of the Table1 table is: Beijing; The value of the City field of the Table2 table is: Hong Kong, Zhangjiagang, Beijing, Shanghai
To query the data in the Table1 table using the City Field Association Table1,table2 table, we first think of the query in the form of (example: SELECT * FROM table1 where field in (select field from Table2)). However, the result of this query is not correct, careful observation will find that if used in, the value of the City field of the Table2 table must be (' Hong Kong ', ' Zhangjiagang ', ' Beijing ', ' Shanghai ') format, so that the results of the query will be correct, At this point, if we use the following SQL to help us solve the problem.

Cases:Select *  from Table whereFieldinch (  withA as(SELECT(SelectField fromtable2) A fromDUAL)SELECTDECODE (B,0, SUBSTR (A,c), SUBSTR (a,c,b-C) City from(SELECTA, A, (LAG (B,1,0) Over(ORDER  byLV))+1C from(SELECTA,instr (A,',',1, LevelB LevelLv fromAconnect by  Level <=(LENGTH (A)-LENGTH (REPLACEA',',"')))+1)))

2. Method Two: Use regular expressions in Oracle Regexp_substr

 with Temp  as     (SELECT 'Hong Kong, Zhangjiagang, Beijing, Shanghai, 95,aa' text         fromDUAL)SELECTREGEXP_SUBSTR (text,'[^,]+',1, RN) City from TempT1, (SELECT      LevelRN fromDUAL CONNECT by  Level <=                      (SELECTLENGTH (text)                              -LENGTH (REPLACE(text,',',"'))                              + 1                          from Temp)) T2

3, method Three: Use of the table (Fw_answer)

Select  from Fw_answer      ANSWER   -----------------   a,b,c   a,f   b,d,e   D

To change the comma-delimited column into a row display, here is the Substr method, as follows:

 SelectSUBSTR (Answer,instr (','||Answer|| ',',',',1, T2.row_num), InStr (','||Answer|| ',',',',1, T2.row_num+1)-1-InStr','||Answer',',1, T2.row_num)) Answer fromFw_answer T1, (SelectRowNum row_num fromUser_objectswhereRowNum<= Ten) T2whereNVL (substr (Answer,instr (','||Answer||',',',',1, T2.row_num), InStr (','||Answer||',',',',1, T2.row_num+1)-1-InStr','||Answer',',1, T2.row_num)),'-')!='-'   Order  byAnswer

Query Result:

  ANSWER    -----------------    a    a    b    b    C    D    D    E    F   

"If you are separating with other characters, you can do so in the same way, just replace the comma with the character." 】
The above method is for the character store irregular situation, for the Fw_answer table in the answer column is a regular follow, so the simplified SQL is as follows:

  SelectSUBSTR (Answer,t2.row_num*2-1,1) Answer fromFw_answer T1, (SelectRowNum row_num fromUser_objectswhereRowNum<= Ten) T2whereNVL (substr (answer,t2.row_num*2-1,1),'-')!='-'   Order  byAnswer

Note: User_objects mainly describes all the objects that are established by the current user through the DDL. Includes tables, views, indexes, stored procedures, triggers,
Packages, indexes, sequences, and so on. is a view of the Oracle dictionary table. This can also be done in other ways, such as dual, where the content by
So don't dual, use user_objects. 】

------------------------------------------------------------------------------------------------------

Just the opposite: convert columns to Rows!

From the Internet to find all is about Decode method realization of the column change, and later found that with Orcale Wmsys.wm_concat method can be easily implemented, the following example is on-line search: Wmsys.wm_concat to 10g later can.

table structure:1A1B1C2A2B3C3F4D converted into:1A,b,c2A, b3c,f4D

Method:

Assuming that your table structure is tb_name (ID, remark), the statement is as follows:

SELECT  from Group  by

How to use SQL to convert a string separated by commas into a list (go)