sql - ओरेकल में कई पंक्तियों में स्ट्रिंग को विभाजित करना




plsql oracle10g (10)

मुझे पता है कि PHP और MYSQL के साथ कुछ डिग्री का उत्तर दिया गया है, लेकिन मैं सोच रहा था कि कोई मुझे स्ट्रिंग (कॉमा सीमांकित) को ओरेकल 10 जी (अधिमानतः) और 11 जी में एकाधिक पंक्तियों में विभाजित करने का सबसे आसान तरीका सिखा सकता है।

तालिका निम्नानुसार है:

Name | Project | Error 
108    test      Err1, Err2, Err3
109    test2     Err1

मैं निम्नलिखित बनाना चाहता हूं:

Name | Project | Error
108    Test      Err1
108    Test      Err2 
108    Test      Err3 
109    Test2     Err1

मैंने स्टैक के आस-पास कुछ संभावित समाधान देखे हैं, हालांकि वे केवल एक कॉलम के लिए जिम्मेदार हैं (अल्पविराम सीमांकित स्ट्रिंग होने के नाते)। किसी भी तरह की सहायता का स्वागत किया जाएगा।

https://code.i-harness.com


OEGcle 11i तक REGEXP_COUNT नहीं जोड़ा गया था। कला के समाधान से अपनाया गया ओरेकल 10 जी समाधान यहां दिया गया है।

SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
  FROM dual
CONNECT BY LEVEL <=
  LENGTH('Err1, Err2, Err3')
    - LENGTH(REPLACE('Err1, Err2, Err3', ',', ''))
    + 1;

XMLTABLE का उपयोग करके एक वैकल्पिक कार्यान्वयन यहां दिया गया है जो विभिन्न डेटा प्रकारों को कास्टिंग करने की अनुमति देता है:

select 
  xmltab.txt
from xmltable(
  'for $text in tokenize("a,b,c", ",") return $text'
  columns 
    txt varchar2(4000) path '.'
) xmltab
;

... या यदि आपके सीमित तारों को तालिका के एक या अधिक पंक्तियों में संग्रहीत किया जाता है:

select 
  xmltab.txt
from (
  select 'a;b;c' inpt from dual union all
  select 'd;e;f' from dual
) base
inner join xmltable(
  'for $text in tokenize($input, ";") return $text'
  passing base.inpt as "input"
  columns 
    txt varchar2(4000) path '.'
) xmltab
  on 1=1
;

ओरेकल 12 सी से शुरू करने से आप JSON_TABLE और JSON_ARRAY उपयोग कर सकते हैं:

CREATE TABLE tab(Name, Project, Error) AS
SELECT 108,'test' ,'Err1, Err2, Err3' FROM dual UNION 
SELECT 109,'test2','Err1'             FROM dual;

और पूछताछ:

SELECT *
FROM tab t
OUTER APPLY (SELECT TRIM(p) AS p
            FROM JSON_TABLE(REPLACE(JSON_ARRAY(t.Error), ',', '","'),
           '$[*]' COLUMNS (p VARCHAR2(4000) PATH '$'))) s;

आउटपुट:

┌──────┬─────────┬──────────────────┬──────┐
│ Name │ Project │      Error       │  P   │
├──────┼─────────┼──────────────────┼──────┤
│  108 │ test    │ Err1, Err2, Err3 │ Err1 │
│  108 │ test    │ Err1, Err2, Err3 │ Err2 │
│  108 │ test    │ Err1, Err2, Err3 │ Err3 │
│  109 │ test2   │ Err1             │ Err1 │
└──────┴─────────┴──────────────────┴──────┘

नियमित अभिव्यक्ति एक अद्भुत बात है :)

with temp as  (
       select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error  from dual
       union all
       select 109, 'test2', 'Err1' from dual
     )

SELECT distinct Name, Project, trim(regexp_substr(str, '[^,]+', 1, level)) str
  FROM (SELECT Name, Project, Error str FROM temp) t
CONNECT BY instr(str, ',', 1, level - 1) > 0
order by Name

बड़े डेटासेट का उपयोग करते समय स्वीकार किए गए उत्तर में खराब प्रदर्शन होता है।

यह एक बेहतर तरीका हो सकता है (regexp के साथ भी और कनेक्ट):

with temp as
(
    select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.error, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
order by name

संपादित करें : यहां एक सरल (जैसा कि "गहराई में नहीं है") क्वेरी का स्पष्टीकरण है।

  1. length (regexp_replace(t.error, '[^,]+')) + 1 regexp_replace का उपयोग किसी भी चीज़ को मिटाने के लिए करता है जो delimiter (इस मामले में अल्पविराम) नहीं है और यह regexp_replace करने के लिए length +1 कितने तत्व (त्रुटियां) हैं।
  2. select level from dual connect by level <= (...) 1 से लेकर त्रुटियों की कुल संख्या में मिले मिलानों की बढ़ती संख्या के साथ कॉलम बनाने के लिए एक पदानुक्रमित क्वेरी का उपयोग करता है।

    पूर्वावलोकन:

    select level, length (regexp_replace('Err1, Err2, Err3', '[^,]+'))  + 1 as max 
    from dual connect by level <= length (regexp_replace('Err1, Err2, Err3', '[^,]+'))  + 1
    
  3. table(cast(multiset(.....) as sys.OdciNumberList)) प्रकार के कुछ कास्टिंग करता है।
    • cast(multiset(.....)) as sys.OdciNumberList संग्रह, cast(multiset(.....)) as sys.OdciNumberList एकाधिक संग्रह (मूल डेटा सेट में प्रत्येक पंक्ति के लिए एक संग्रह) को परिवर्तित करता है।
    • table() फ़ंक्शन संग्रह को एक परिणाम में बदल देता है।
  4. किसी भी शामिल होने के बिना आपके डेटासेट और मल्टीसेट के बीच एक क्रॉस जॉइन बनाता है। नतीजतन, 4 मैचों के साथ सेट डेटा में एक पंक्ति 4 बार दोहराएगी ("कॉलम_वल्यू" नामक कॉलम में बढ़ती संख्या के साथ)।

    पूर्वावलोकन:

    select * from 
    temp t,
    table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.error, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
    
  5. trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value)) column_value के लिए column_value को nth_appearance / ocurrence पैरामीटर के रूप में उपयोग करता है।
  6. आप आसानी से विज़ुअलाइज़ेशन के लिए अपने डेटा सेट ( t.name, t.project उदाहरण के रूप में) से कुछ अन्य कॉलम जोड़ सकते हैं।

ओरेकल दस्तावेज़ों के कुछ संदर्भ:


मुझे एक ही समस्या थी, और xmltable ने मेरी मदद की:

SELECT id, trim(COLUMN_VALUE) text 
FROM t, xmltable(('"'  || REPLACE(text, ',', '","') || '"'))

मैं एक और तरीका जोड़ना चाहता हूं। यह रिकर्सिव क्वेरी का उपयोग करता है, जो मैंने अन्य उत्तरों में नहीं देखा है। यह ओआरकल द्वारा 11 जीआर 2 के बाद समर्थित है।

with cte0 as (
    select phone_number x
    from hr.employees
), cte1(xstr,xrest,xremoved) as (
        select x, x, null
        from cte0
    union all        
        select xstr,
            case when instr(xrest,'.') = 0 then null else substr(xrest,instr(xrest,'.')+1) end,
            case when instr(xrest,'.') = 0 then xrest else substr(xrest,1,instr(xrest,'.') - 1) end
        from cte1
        where xrest is not null
)
select xstr, xremoved from cte1  
where xremoved is not null
order by xstr

यह विभाजन चरित्र के साथ काफी लचीला है। बस इसे INSTR कॉल में बदलें।


मैं एक पाइपलाइन टेबल फ़ंक्शन का उपयोग करके एक अलग दृष्टिकोण का प्रस्ताव देना चाहता हूं। यह XMLTABLE की तकनीक के समान कुछ है, सिवाय इसके कि आप चरित्र स्ट्रिंग को विभाजित करने के लिए अपना स्वयं का कस्टम फ़ंक्शन प्रदान कर रहे हैं:

-- Create a collection type to hold the results
CREATE OR REPLACE TYPE typ_str2tbl_nst AS TABLE OF VARCHAR2(30);
/

-- Split the string according to the specified delimiter
CREATE OR REPLACE FUNCTION str2tbl (
  p_string    VARCHAR2,
  p_delimiter CHAR DEFAULT ',' 
)
RETURN typ_str2tbl_nst PIPELINED
AS
  l_tmp VARCHAR2(32000) := p_string || p_delimiter;
  l_pos NUMBER;
BEGIN
  LOOP
    l_pos := INSTR( l_tmp, p_delimiter );
    EXIT WHEN NVL( l_pos, 0 ) = 0;
    PIPE ROW ( RTRIM( LTRIM( SUBSTR( l_tmp, 1, l_pos-1) ) ) );
    l_tmp := SUBSTR( l_tmp, l_pos+1 );
  END LOOP;
END str2tbl;
/

-- The problem solution
SELECT name, 
       project, 
       TRIM(COLUMN_VALUE) error
  FROM t, TABLE(str2tbl(error));

परिणाम:

      NAME PROJECT    ERROR
---------- ---------- --------------------
       108 test       Err1
       108 test       Err2
       108 test       Err3
       109 test2      Err1

इस प्रकार के दृष्टिकोण के साथ समस्या यह है कि अक्सर ऑप्टिमाइज़र टेबल फ़ंक्शन की कार्डिनालिटी नहीं जान पाएगा और इसे अनुमान लगाना होगा। यह आपके निष्पादन योजनाओं के लिए संभावित हानिकारक हो सकता है, इसलिए इस समाधान को अनुकूलक के लिए निष्पादन आंकड़े प्रदान करने के लिए बढ़ाया जा सकता है।

आप ऊपर दिए गए प्रश्न पर एक EXPLAIN योजना चलाकर इस अनुकूलक अनुमान को देख सकते हैं:

Execution Plan
----------------------------------------------------------
Plan hash value: 2402555806

----------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         | 16336 |   366K|    59   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                      |         | 16336 |   366K|    59   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                | T       |     2 |    42 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    28   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

भले ही संग्रह में केवल 3 मान हैं, ऑप्टिमाइज़र ने इसके लिए 8168 पंक्तियों का अनुमान लगाया है (डिफ़ॉल्ट मान)। यह पहली बार अप्रासंगिक प्रतीत हो सकता है, लेकिन यह ऑप्टिमाइज़र के लिए उप-इष्टतम योजना का निर्णय लेने के लिए पर्याप्त हो सकता है।

समाधान संग्रह के आंकड़े प्रदान करने के लिए अनुकूलक एक्सटेंशन का उपयोग करना है:

-- Create the optimizer interface to the str2tbl function
CREATE OR REPLACE TYPE typ_str2tbl_stats AS OBJECT (
  dummy NUMBER,

  STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )
  RETURN NUMBER,

  STATIC FUNCTION ODCIStatsTableFunction ( p_function  IN  SYS.ODCIFuncInfo,
                                           p_stats     OUT SYS.ODCITabFuncStats,
                                           p_args      IN  SYS.ODCIArgDescList,
                                           p_string    IN  VARCHAR2,
                                           p_delimiter IN  CHAR DEFAULT ',' )
  RETURN NUMBER
);
/

-- Optimizer interface implementation
CREATE OR REPLACE TYPE BODY typ_str2tbl_stats
AS
  STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )
  RETURN NUMBER
  AS
  BEGIN
    p_interfaces := SYS.ODCIObjectList ( SYS.ODCIObject ('SYS', 'ODCISTATS2') );
    RETURN ODCIConst.SUCCESS;
  END ODCIGetInterfaces;

  -- This function is responsible for returning the cardinality estimate
  STATIC FUNCTION ODCIStatsTableFunction ( p_function  IN  SYS.ODCIFuncInfo,
                                           p_stats     OUT SYS.ODCITabFuncStats,
                                           p_args      IN  SYS.ODCIArgDescList,
                                           p_string    IN  VARCHAR2,
                                           p_delimiter IN  CHAR DEFAULT ',' )
  RETURN NUMBER
  AS
  BEGIN
    -- I'm using basically half the string lenght as an estimator for its cardinality
    p_stats := SYS.ODCITabFuncStats( CEIL( LENGTH( p_string ) / 2 ) );
    RETURN ODCIConst.SUCCESS;
  END ODCIStatsTableFunction;

END;
/

-- Associate our optimizer extension with the PIPELINED function   
ASSOCIATE STATISTICS WITH FUNCTIONS str2tbl USING typ_str2tbl_stats;

परिणामस्वरूप निष्पादन योजना का परीक्षण करना:

Execution Plan
----------------------------------------------------------
Plan hash value: 2402555806

----------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |     1 |    23 |    59   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                      |         |     1 |    23 |    59   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                | T       |     2 |    42 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH| STR2TBL |     1 |     2 |    28   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

जैसा कि आप ऊपर की योजना पर कार्डिनिटी देख सकते हैं, अब 8196 अनुमानित मूल्य नहीं है। यह अभी भी सही नहीं है क्योंकि हम फ़ंक्शन के लिए शाब्दिक स्ट्रिंग के बजाय कॉलम पास कर रहे हैं।

इस विशेष मामले में एक करीबी अनुमान देने के लिए फंक्शन कोड में कुछ बदलाव करना आवश्यक होगा, लेकिन मुझे लगता है कि समग्र अवधारणा यहां काफी समझाई गई है।

इस उत्तर में इस्तेमाल किए गए str2tbl फ़ंक्शन को मूल रूप से टॉम क्यटे द्वारा विकसित किया गया था: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

ऑब्जेक्ट प्रकारों के साथ आंकड़ों को जोड़ने की अवधारणा को इस लेख को पढ़कर आगे की खोज की जा सकती है: http://www.oracle-developer.net/display.php?id=427

यहां वर्णित तकनीक 10 जी + में काम करती है।


कनेक्ट या regexp से कनेक्ट का उपयोग किए बिना:

    with mytable as (
      select 108 name, 'test' project, 'Err1,Err2,Err3' error from dual
      union all
      select 109, 'test2', 'Err1' from dual
    )
    ,x as (
      select name
      ,project
      ,','||error||',' error
      from mytable
    )
    ,iter as (SELECT rownum AS pos
        FROM all_objects
    )
    select x.name,x.project
    ,SUBSTR(x.error
      ,INSTR(x.error, ',', 1, iter.pos) + 1
      ,INSTR(x.error, ',', 1, iter.pos + 1)-INSTR(x.error, ',', 1, iter.pos)-1
    ) error
    from x, iter
    where iter.pos < = (LENGTH(x.error) - LENGTH(REPLACE(x.error, ','))) - 1;

CREATE FUNCTION dbo.BreakStringIntoRows (@CommadelimitedString   varchar(1000))
RETURNS   @Result TABLE (Column1   VARCHAR(100))
AS
BEGIN
        DECLARE @IntLocation INT
        WHILE (CHARINDEX(',',    @CommadelimitedString, 0) > 0)
        BEGIN
              SET @IntLocation =   CHARINDEX(',',    @CommadelimitedString, 0)      
              INSERT INTO   @Result (Column1)
              --LTRIM and RTRIM to ensure blank spaces are   removed
              SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,   0, @IntLocation)))   
              SET @CommadelimitedString = STUFF(@CommadelimitedString,   1, @IntLocation,   '') 
        END
        INSERT INTO   @Result (Column1)
        SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed
        RETURN 
END
GO

--Using the UDF to convert comma separated values into rows
SELECT * FROM dbo.BreakStringIntoRows('Apple,Banana,Orange')
SELECT * FROM dbo.BreakStringIntoRows('Apple   ,    Banana,    Orange')




oracle11g