Forum Stats

  • 3,735,134 Users
  • 2,247,117 Discussions
  • 7,857,705 Comments

Discussions

Best ways to extract COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|........|COLUMNn|va

carmac
carmac Member Posts: 117
edited Aug 19, 2020 3:52AM in SQL & PL/SQL

Hi All,

I need to extract around 500+ columns and 5+ millions rows data of below type records.

COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|........|COLUMNn|valuen|

with t as

     (select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' as data_column

        from dual)

select *

from (  select *

from t);

Thank you very much for your consideration.

-Thanks

PaulzipWilliam Robertson

Best Answer

  • mathguy
    mathguy Member Posts: 9,784 Gold Crown
    edited Aug 9, 2020 3:13AM Accepted Answer

    500+ columns? That already suggests there's something seriously wrong with your data model.

    In any case - standard INSTR and SUBSTR should be quite a bit faster than regular expressions. Something like this:

    with  t as (    select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' as data_column    from   dual  )select substr(data_column, instr(data_column, '|', 1, 1) + 1,               instr(data_column, '|', 1, 2) - instr(data_column, '|', 1, 1) - 1) as column1     , substr(data_column, instr(data_column, '|', 1, 3) + 1,              instr(data_column, '|', 1, 4) - instr(data_column, '|', 1, 3) - 1) as column2     , substr(data_column, instr(data_column, '|', 1, 5) + 1,              instr(data_column, '|', 1, 6) - instr(data_column, '|', 1, 5) - 1) as column3     , substr(data_column, instr(data_column, '|', 1, 7) + 1,              instr(data_column, '|', 1, 8) - instr(data_column, '|', 1, 7) - 1) as column4from   t;COLUMN1  COLUMN2  COLUMN3  COLUMN4-------- -------- -------- --------value1   value2   value3   value4 

    Left to you for your amusement - write a bit of code, in whatever language, to generate the SQL statement to do this for "500+ columns" - you definitely don't want to do that by hand, right?

    William Robertson

Answers

  • mathguy
    mathguy Member Posts: 9,784 Gold Crown
    edited Aug 9, 2020 3:13AM Accepted Answer

    500+ columns? That already suggests there's something seriously wrong with your data model.

    In any case - standard INSTR and SUBSTR should be quite a bit faster than regular expressions. Something like this:

    with  t as (    select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' as data_column    from   dual  )select substr(data_column, instr(data_column, '|', 1, 1) + 1,               instr(data_column, '|', 1, 2) - instr(data_column, '|', 1, 1) - 1) as column1     , substr(data_column, instr(data_column, '|', 1, 3) + 1,              instr(data_column, '|', 1, 4) - instr(data_column, '|', 1, 3) - 1) as column2     , substr(data_column, instr(data_column, '|', 1, 5) + 1,              instr(data_column, '|', 1, 6) - instr(data_column, '|', 1, 5) - 1) as column3     , substr(data_column, instr(data_column, '|', 1, 7) + 1,              instr(data_column, '|', 1, 8) - instr(data_column, '|', 1, 7) - 1) as column4from   t;COLUMN1  COLUMN2  COLUMN3  COLUMN4-------- -------- -------- --------value1   value2   value3   value4 

    Left to you for your amusement - write a bit of code, in whatever language, to generate the SQL statement to do this for "500+ columns" - you definitely don't want to do that by hand, right?

    William Robertson
  • carmac
    carmac Member Posts: 117
    edited Aug 9, 2020 3:57AM

    Thanks for your response,

    The column and value pairs are not fixed in every row. Some time I may get 100 column value pairs and some time I get 10  column value pairs ....

    is the above query works that scenario also?

    Thanks in advance.

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Aug 9, 2020 4:09AM
    carmac wrote:Thanks for your response,The column and value pairs are not fixed in every row. Some time I may get 100 column value pairs and some time I get 10 column value pairs ....is the above query works that scenario also?Thanks in advance.

    It's your problem and So, YOU need to provide the full test case, "Some time I may get 100 column value pairs and some time I get 10 column value pairs" what does this actually mean?

    Can you get something like CLOUM3|val3|COMULN4|val4, where some values are missing at the beginning? or it's always be missing at the trailing end like 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3, can anything be missing in the middle? Is the missing value means NULL or something else? Is, the COLUMNn part will be there always? is that COLUMNn significant? Is the separator will be there always like COLUMN1|value1|||COLUMN3|value3COLUMN4|value4  What is your database version?

    William Robertson
  • carmac
    carmac Member Posts: 117
    edited Aug 9, 2020 4:32AM

    Hi,

    My database version is 11g and my data appears like below.

    COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|

    COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|

    COLUMN2|value2|COLUMN3|value3|

    COLUMN1|value1|COLUMN4|value4|

    COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|

    COLUMN4|value4|

    COLUMN3|value3|

    COLUMN1|value1|COLUMN2|value2|

    COLUMN2|value2|

    COLUMN1|value1|

    -Thanks

  • Ranagal
    Ranagal Member Posts: 634 Bronze Badge
    edited Aug 9, 2020 5:43AM

    Dude,

    Your data is the perfect example for the worst design. Have you ever come across a design concept called 'Normalization' ? If not, kindly ready what 1 NF is. If you understand, go for 2NF, 3NF and even BCNF.

    If you can, then change the current design to follow the Normalization rules. If you can't change the design at this phase, then bring it to manager's notice and only then go for what is known as Dynamic SQL.

    If you don't know what Dynamic SQL is, then kindly do a Google search. There are many examples.

    Regards,

    Ranagal

    Paulzip
  • carmac
    carmac Member Posts: 117
    edited Aug 9, 2020 5:50AM

    Using below I am getting the results as required, still there is some scope to optimize below query. Please advise if you have any ideas.

    Really appreciate in advance.

    with

      t as (

        select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' as data_column

        from   dual

      )

    select

        substr(data_column, instr(data_column, 'COLUMN1', 1, 1)+length('COLUMN1')+1,

            instr(substr(data_column, instr(data_column, 'COLUMN1', 1, 1)), '|',1,2) -

            instr(substr(data_column, instr(data_column, 'COLUMN1', 1, 1)), '|',1,1)-1) as col1,

        substr(data_column, instr(data_column, 'COLUMN3', 1, 1)+length('COLUMN3')+1,

            instr(substr(data_column, instr(data_column, 'COLUMN3', 1, 1)), '|',1,2) -

            instr(substr(data_column, instr(data_column, 'COLUMN3', 1, 1)), '|',1,1)-1) as col3

    from t;

    -Thanks

  • Paulzip
    Paulzip Member Posts: 8,328 Blue Diamond
    edited Aug 9, 2020 6:05AM

    This is a terrible data format design, it actually shocks me how bad it is. It's inefficient, unnormalised it'll probably rely on dynamic solutions. Is this your design?  Is there any scope to change it?

    William Robertson
  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited Aug 9, 2020 7:42AM

    It's almost a compressed form of JSON or XML. It might be a fun exercise to see if you can transform the raw data into one of those two formats and then use built-in tools to parse the result. Whether this will be any faster than the current approach is anybody's guess, though.

    Paulzip
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,409 Black Diamond
    edited Aug 9, 2020 8:57AM

    OP is on 11g, so no JSON. But below is JSON solution for 12C:

    SELECT  TBL.ROWID RID,

            COLUMN_NAME,

            COLUMN_VALUE

      FROM  TBL,

            JSON_TABLE(

                       '[{' || RTRIM(REGEXP_REPLACE(STR,'([^|]+)\|([^|]+)\|','"COLUMN":"\1","VALUE":"\2"},{'),',{') || ']',

                       '$[*]'

                       COLUMNS(

                               COLUMN_NAME  VARCHAR2(30) PATH '$.COLUMN',

                               COLUMN_VALUE VARCHAR2(30) PATH '$.VALUE'

                              )

                      )

    /

    RID                COLUMN_NAME                    COLUMN_VALUE

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

    AAAcDAAALAAAHQbAAA COLUMN1                        value1

    AAAcDAAALAAAHQbAAA COLUMN2                        value2

    AAAcDAAALAAAHQbAAA COLUMN3                        value3

    AAAcDAAALAAAHQbAAA COLUMN4                        value4

    AAAcDAAALAAAHQbAAB COLUMN1                        value1

    AAAcDAAALAAAHQbAAB COLUMN3                        value3

    AAAcDAAALAAAHQbAAB COLUMN4                        value4

    AAAcDAAALAAAHQbAAC COLUMN2                        value2

    AAAcDAAALAAAHQbAAC COLUMN3                        value3

    AAAcDAAALAAAHQbAAD COLUMN1                        value1

    AAAcDAAALAAAHQbAAD COLUMN4                        value4

    AAAcDAAALAAAHQbAAE COLUMN2                        value2

    AAAcDAAALAAAHQbAAE COLUMN3                        value3

    AAAcDAAALAAAHQbAAE COLUMN4                        value4

    AAAcDAAALAAAHQbAAF COLUMN4                        value4

    AAAcDAAALAAAHQbAAG COLUMN3                        value3

    AAAcDAAALAAAHQbAAH COLUMN1                        value1

    AAAcDAAALAAAHQbAAH COLUMN2                        value2

    AAAcDAAALAAAHQbAAI COLUMN2                        value2

    AAAcDAAALAAAHQbAAJ COLUMN1                        value1

    20 rows selected.

    SQL>

    SY.

    William Robertson
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,409 Black Diamond
    edited Aug 9, 2020 9:14AM

    XML solution - will work in 11G:

    SELECT  TBL.ROWID RID,

            COLUMN_NAME,

            COLUMN_VALUE

      FROM  TBL,

            XMLTABLE(

                     '/DOC/SET'

                     PASSING XMLTYPE('<DOC>' || REGEXP_REPLACE(STR,'([^|]+)\|([^|]+)\|','<SET><COLUMN>\1</COLUMN><VALUE>\2</VALUE></SET>') || '</DOC>')

                      COLUMNS

                        COLUMN_NAME  VARCHAR2(30) PATH '/SET/COLUMN',

                        COLUMN_VALUE VARCHAR2(30) PATH '/SET/VALUE'

                     )

    /

    RID                COLUMN_NAME                    COLUMN_VALUE

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

    AAAcDAAALAAAHQbAAA COLUMN1                        value1

    AAAcDAAALAAAHQbAAA COLUMN2                        value2

    AAAcDAAALAAAHQbAAA COLUMN3                        value3

    AAAcDAAALAAAHQbAAA COLUMN4                        value4

    AAAcDAAALAAAHQbAAB COLUMN1                        value1

    AAAcDAAALAAAHQbAAB COLUMN3                        value3

    AAAcDAAALAAAHQbAAB COLUMN4                        value4

    AAAcDAAALAAAHQbAAC COLUMN2                        value2

    AAAcDAAALAAAHQbAAC COLUMN3                        value3

    AAAcDAAALAAAHQbAAD COLUMN1                        value1

    AAAcDAAALAAAHQbAAD COLUMN4                        value4

    AAAcDAAALAAAHQbAAE COLUMN2                        value2

    AAAcDAAALAAAHQbAAE COLUMN3                        value3

    AAAcDAAALAAAHQbAAE COLUMN4                        value4

    AAAcDAAALAAAHQbAAF COLUMN4                        value4

    AAAcDAAALAAAHQbAAG COLUMN3                        value3

    AAAcDAAALAAAHQbAAH COLUMN1                        value1

    AAAcDAAALAAAHQbAAH COLUMN2                        value2

    AAAcDAAALAAAHQbAAI COLUMN2                        value2

    AAAcDAAALAAAHQbAAJ COLUMN1                        value1

    20 rows selected.

    SQL>

    SY.

    William Robertson
  • carmac
    carmac Member Posts: 117
    edited Aug 9, 2020 9:24AM

    Thank you all I really appreciate your help.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,409 Black Diamond
    edited Aug 9, 2020 9:32AM

    Hierarchical no regexp solution (will work in 11G):

    SELECT  ROWID RID,

            SUBSTR(STR,INSTR('|' || STR,'|',1,2 * LEVEL - 1),INSTR(STR,'|',1,2 * LEVEL - 1) - INSTR('|' || STR,'|',1,2 * LEVEL - 1)) COLUMN_NAME,

            SUBSTR(STR,INSTR(STR,'|',1,2 * LEVEL - 1) + 1,INSTR(STR,'|',1,2 * LEVEL) - INSTR(STR,'|',1,2 * LEVEL - 1) - 1) COLUMN_VALUE

      FROM  TBL

      CONNECT BY ROWID = PRIOR ROWID

             AND PRIOR SYS_GUID() IS NOT NULL

             AND INSTR(STR,'|',1,2 * LEVEL) != 0

    /

    RID                COLUMN_NAME     COLUMN_VALUE

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

    AAAcDAAALAAAHQbAAA COLUMN1         value1

    AAAcDAAALAAAHQbAAA COLUMN2         value2

    AAAcDAAALAAAHQbAAA COLUMN3         value3

    AAAcDAAALAAAHQbAAA COLUMN4         value4

    AAAcDAAALAAAHQbAAB COLUMN1         value1

    AAAcDAAALAAAHQbAAB COLUMN3         value3

    AAAcDAAALAAAHQbAAB COLUMN4         value4

    AAAcDAAALAAAHQbAAC COLUMN2         value2

    AAAcDAAALAAAHQbAAC COLUMN3         value3

    AAAcDAAALAAAHQbAAD COLUMN1         value1

    AAAcDAAALAAAHQbAAD COLUMN4         value4

    AAAcDAAALAAAHQbAAE COLUMN2         value2

    AAAcDAAALAAAHQbAAE COLUMN3         value3

    AAAcDAAALAAAHQbAAE COLUMN4         value4

    AAAcDAAALAAAHQbAAF COLUMN4         value4

    AAAcDAAALAAAHQbAAG COLUMN3         value3

    AAAcDAAALAAAHQbAAH COLUMN1         value1

    AAAcDAAALAAAHQbAAH COLUMN2         value2

    AAAcDAAALAAAHQbAAI COLUMN2         value2

    AAAcDAAALAAAHQbAAJ COLUMN1         value1

    20 rows selected.

    SQL>

    SY.

    William Robertson
  • mathguy
    mathguy Member Posts: 9,784 Gold Crown
    edited Aug 9, 2020 10:57AM
    carmac wrote:Hi,My database version is 11g and my data appears like below.COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4| COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|COLUMN2|value2|COLUMN3|value3|COLUMN1|value1|COLUMN4|value4| COLUMN2|value2|COLUMN3|value3|COLUMN4|value4| COLUMN4|value4| COLUMN3|value3|COLUMN1|value1|COLUMN2|value2|COLUMN2|value2|COLUMN1|value1|-Thanks

    First, let's be clear regarding the desired output.

    In your original post, you show that you need the "values" to line up into columns, one row of input resulting in one row of output. Compare that to what Solomon Yakobson proposed in Replies 9, 10 and 12: the output is presented in an entirely different format. Your format (with multiple columns) is the proper relational arrangement, if the columns all mean different things (if they represent different attributes of the same entity). The format in SY's replies is entity-attribute-value, not a good arrangement in relational databases. That format is more appropriate when all the tokens are "of the same kind" - which doesn't seem to be your case. Anyway, you need to confirm the format you need.

    Then: assuming that you do, indeed, need a "relational" output, you must know the number of columns in the output before you write the query. Most importantly, the number of columns must be the same in every row; the data you show above makes no sense. There is no concept of table in which one row has four columns, another only two columns, and some rows only have column 2 and column 4 but no column 1 and no column 3. If you can show us how that would look, then perhaps we can help, but at this point it's not clear what the desired output should look like, with the input I copied and pasted above.

    What my query will do in this case: You must hard-code the number of columns. Let's call that number "n". Then, if an input row has more than n column-value pairs, the output will only show the values from the first n columns, ignoring the remaining ones. If an input row has fewer than n column-value pairs, the output will show all the values in the first columns of output, filling the remaining columns with NULL. And, if your input only has COLUMN2 and COLUMN4 values but no COLUMN1 and COLUMN3, then value2 and value4 will appear in the first two columns (not in columns 2 and 4).

    Note that that's exactly what YOUR query will do. Your query that you had in the original post, and that you have now deleted for some reason. Why did you do that? That is very uncool.

    If you are OK with hardcoding a (maximum) number of columns, accepting that rows that have more column-value pairs will lose information and that columns that don't have a value in the input will get NULL in the output (so, if the input has COLUMN2 and COLUMN4 values, they will appear in the proper columns in the output), that can be done too, but the code is more complicated. You will still want to do everything with standard INSTR and SUBSTR; you will NOT want to convert the inputs to JSON or XML, and you will NOT want to get the output in EAV format, only to pivot back into single rows of output for each input row; both of those operations will make execution time worse. I'll rig a solution with INSTR/SUBSTR for your example to show what I mean, when I get a few minutes.   EDIT:  Actually, now that I took a closer look at some of the Replies, I see what you are doing in Reply 6. That's the approach I have in mind. The way you wrote it is partially wrong: if the input row does not have COLUMN1 but it does have COLUMN10, it will match COLUMN10 where you are seeking 'COLUMN1' (and the length computations won't match so you won't even get 'value10' in the first column, you'll rather get something like '|value1' (chopping off the last character from 'value10' and instead showing a leading pipe symbol). But all of that can be fixed. Whether it can be made faster is a different question, I suspect not. You can try the different answers offered here; it would be good to let us know what you find when you test on your actual data.

    William Robertson
  • Paulzip
    Paulzip Member Posts: 8,328 Blue Diamond
    edited Aug 9, 2020 11:01AM
    William Robertson wrote:It's almost a compressed form of JSON or XML. It might be a fun exercise to see if you can transform the raw data into one of those two formats and then use built-in tools to parse the result. Whether this will be any faster than the current approach is anybody's guess, though.

    Yes, when I first saw it I thought "That might be better as JSON", but you could argue it would be a lot easier to just get rid of the columnN|valueN|.... approach and just do it as a delimited file of values and consume it with an external table + merge / insert.

    COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|

    COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|

    COLUMN2|value2|COLUMN3|value3|

    COLUMN1|value1|COLUMN4|value4|

    becomes....

    value1|value2|value3|value4

    value1||value3|value4

    |value2|value3|

    value1|||value4

    Personally I think OP's approach is about as bad as it gets.

  • mathguy
    mathguy Member Posts: 9,784 Gold Crown
    edited Aug 9, 2020 11:30AM

    Working on a solution for the inputs you show in Reply 4, I realized that your solution from Reply 6 will do even worse than I explained in Reply 13. If 'COLUMN1' is not found at all, the corresponding INSTR is 0, and the query will work from that. It will always produce something in column 1 of the output, even when there is no 'COLUMN1|' in the input.

    The following solution is better. It will still go wrong if the value part of a pair may contain the substring COLUMN<number> right at the end. For example, it will give the wrong result on the following input:

    COLUMN1|value is 2 * COLUMN2|COLUMN2|something|

    Do you see how    COLUMN2|   appears in the input string, as part of "value1|", sooner than we expect to see it? That will make the whole thing blow up. You can mitigate it somewhat by doubling the pipe symbols first (and adding pipes at the ends of the string), but even that will fail on the following input:

    COLUMN1|COLUMN2|COLUMN2|value2|

    (that is, in this arrangement the value1 string is exactly 'COLUMN2')  This too can be fixed, but it becomes more and more complicated, and you still won't know for sure that you didn't miss some other obscure special case.

    In the solution below, I assume two things: (1) The pipe symbol can only appear as terminator (for "names" and "values" in column name - value pairs), and (2) the "value" part of the pairs can't have the substring COLUMN<number> at the end.  With these assumptions, I believe the following query will do what you need. Remember that input "columns" beyond what is hardcoded in the query will simply be ignored. This is illustrated in the inputs I chose.

    with  t (data_column) as (    select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' from dual union all    select 'COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|'                from dual union all    select 'COLUMN2|value2|COLUMN3|value3|'                               from dual union all    select 'COLUMN1|value1|COLUMN4|value4|'                               from dual union all    select 'COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|'                from dual union all    select 'COLUMN4|value4|'                                              from dual union all    select 'COLUMN3|value3|'                                              from dual union all    select 'COLUMN1|value1|COLUMN3|value3|'                               from dual union all    select 'COLUMN2|value2|'                                              from dual union all    select 'COLUMN3|value3|COLUMN10|value10|'                             from dual  )select data_column     , substr(data_column, instr(data_column, 'COLUMN1|') + length('COLUMN1|'),              instr(data_column, '|', instr(data_column, 'COLUMN1|'), 2)                  - instr(data_column, 'COLUMN1|') - length('COLUMN1|')) as column1     , substr(data_column, instr(data_column, 'COLUMN2|') + length('COLUMN2|'),              instr(data_column, '|', instr(data_column, 'COLUMN2|'), 2)                  - instr(data_column, 'COLUMN2|') - length('COLUMN2|')) as column2     , substr(data_column, instr(data_column, 'COLUMN3|') + length('COLUMN3|'),              instr(data_column, '|', instr(data_column, 'COLUMN3|'), 2)                  - instr(data_column, 'COLUMN3|') - length('COLUMN3|')) as column3from  t;DATA_COLUMN                                                  COLUMN1  COLUMN2  COLUMN3------------------------------------------------------------ -------- -------- --------COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4| value1   value2   value3 COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|                value1            value3 COLUMN2|value2|COLUMN3|value3|                                        value2   value3 COLUMN1|value1|COLUMN4|value4|                               value1                   COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|                         value2   value3 COLUMN4|value4|                                                                       COLUMN3|value3|                                                                value3 COLUMN1|value1|COLUMN3|value3|                               value1            value3 COLUMN2|value2|                                                       value2         COLUMN3|value3|COLUMN10|value10|                                               value3 
    William Robertsoncarmac
  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited Aug 9, 2020 1:06PM

    I agree it's a horrible format, but to be fair to the OP it may come from some application that perversely insists on sending it like that. All the same, I would push back and see if anything could be done about the format, or failing that, whether some pre-processing step could be applied in Perl or Python or something that could convert it to regular CSV with all 100 columns per record, or even to XML so at least there would be a supported way to parse it.

    Paulzip
  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited Aug 10, 2020 6:21PM

    Just to confirm once and for all in case I missed a detail, does the real data contain literally COLUMN4 etc, and are those actual column names in the table being loaded, or were those just simplified examples and the real date is more like CUSTOMER_ID, PURCHASE_QUANTITY, CHEESE_TYPE, UNIT_PRICE, DISCOUNT_PERCENT etc?

  • carmac
    carmac Member Posts: 117
    edited Aug 19, 2020 3:08AM

    Hi All,

    please suggest me how to extract and load below type data into another table if the column data type is CLOB. some of the rows of the length is 12000 bytes or characters.

    with

      t (clob_column) as (

        select 'CUST_ID|value1|CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all

        select 'CUST_ID|value1|CUST_DEPT|value3|ITEM_ID|value4|'                from dual union all

        select 'CUST_NAME|value2|CUST_DEPT|value3|'                               from dual union all

        select 'CUST_ID|value1|ITEM_ID|value4|'                               from dual union all

        select 'CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|'                from dual union all

        select 'ITEM_ID|value4|'                                              from dual union all

        select 'CUST_DEPT|value3|'                                              from dual union all

        select 'CUST_ID|value1|CUST_DEPT|value3|'                               from dual union all

        select 'CUST_NAME|value2|'                                              from dual union all

        select 'CUST_DEPT|value3|CUST_ID0|value10|'                             from dual

      )

      select * from t;

    Thanks in advance.

  • Ranagal
    Ranagal Member Posts: 634 Bronze Badge
    edited Aug 19, 2020 3:52AM

    This is a different question in itself than that was asked before for which you even marked the answer. Kindly open a new thread for this. You are better off re-designing the whole process of taking the data in as your current design reeks of violations of basic DB Design Rules.

    Regards,

    Ranagal

Sign In or Register to comment.