Home / Questions / 138166
Accepted
Ricardo Gabriel
Asked: 2020-02-14 06:16:59 +0800 CST 2020-02-14 06:16:59 +0800 CST

How do I separate a string into multiple substrings in different columns in SQL?

  • 772

I am working with OracleXE, I have some files that have the floor or underscore character ( ) in their names _. Example:

uno_dos_tres_cuatro.csv
A_B_C_D.csv
arbol_mesa_pizza_oregano.csv

I want to separate those fields (of different length) into 4 separate columns.

I used the following expression, but that statement only returns the first field in the string:

SELECT REGEXP_SUBSTR(NOMBRE_COMPLETO, '[^_]+[^_]+') FROM ARCHIVO;

My idea is that it returns the 4 fields, without the extension .csvat the end:

| columna 1 | columna 2 | columna 3 | columna 4 |
|-----------|-----------|-----------|-----------|
| uno       | dos       | tres      | cuatro    |
| A         | B         | C         | D         |
| arbol     | mesa      | pizza     | oregano   |

This is an example of my table:

CREATE TABLE tabla ("id" int, "nombre_archivo" varchar2(34));
INSERT ALL 
    INTO tabla ("id", "nombre_archivo")
         VALUES (1, 'uno_dos_tres_cuatro.csv')
    INTO tabla ("id", "nombre_archivo")
         VALUES (2, 'A_B_C_D.csv')
    INTO tabla ("id", "nombre_archivo")
         VALUES (3, 'arbol_mesa_pizza_oregano.csv')
SELECT * FROM dual;
sql

1 Answers

  1. Best Answer

    Separate into columns (general case)

    When the delimiter is a single character , we match any text except the delimiter c:

    [^c]*

    • Example for '_':

      REGEXP_SUBSTR( COLUMNA, '[^_]*', 1, &num_item )

      and when we pass 1 in the parameter &num_item, it will return the first item when separating the string; 2 for the second, etc.


    Instead, if the delimiter has more than one character :

    (.*?)(Texto Delimitador|$)

    • Example for '~~':

      REGEXP_SUBSTR( COLUMNA, '(.*?)(~~|$)', 1, &num_item, '', 1)

      The 1in the last parameter is saying to return the text that matched the first set of parentheses.


    In both cases, if you want to ignore empty items (two delimiters in a row), you have to change the *to a +.


    Separate by removing .csvfrom the end

    The idea is to match:

    • with all characters other than an underscore ( [^_]+)
    • until meeting(_|(\.csv)?$)
      • a _, or
      • (\.csv)?$the file extension (optional) followed by the end of the text.


    So, we have the regex:

    ([^_]+?)(_|(\.csv)?$)

    where what we are interested in obtaining is the text of the first group (the first set of parentheses).


    And we pass that regex to the REGEXP_SUBSTR() function

    REGEXP_SUBSTR( columna, regex, inicio, num_coincidencia, opciones, grupo)
    • num_coincidenciaWe are going to change it for each of the 4 columns.
    • grupowill be 1 (the text that matched the first parentheses)


    Code:

    select REGEXP_SUBSTR( "nombre_archivo" ,'([^_]+?)(_|(\.csv)?$)', 1, 1, '', 1)
           AS "columna 1",
       REGEXP_SUBSTR( "nombre_archivo" ,'([^_]+?)(_|(\.csv)?$)', 1, 2, '', 1)
           AS "columna 2",
       REGEXP_SUBSTR( "nombre_archivo" ,'([^_]+?)(_|(\.csv)?$)', 1, 3, '', 1)
           AS "columna 3",
       REGEXP_SUBSTR( "nombre_archivo" ,'([^_]+?)(_|(\.csv)?$)', 1, 4, '', 1)
           AS "columna 4"
  from tabla

    Result

    | columna 1 | columna 2 | columna 3 | columna 4 |
|-----------|-----------|-----------|-----------|
| uno       | dos       | tres      | cuatro    |
| A         | B         | C         | D         |
| arbol     | mesa      | pizza     | oregano   |

    demonstration:

    http://sqlfiddle.com/#!4/07d59/2/0

    • 4