Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: how to split comma delimited string values

    Hi Friends

    i have a requirement that is i need to split the string into separate values

    EX: i have column like INVOICE_NUMB(varchar2)

    values 123456,567890,123765

    now i need to compare this invoice numbers with Receipt created invoice numbers that column like this REC_INVOICE_NUMB(varchar2)

    values like 123456
    567890
    123765


    now i need to write

    select INVOICE_NUMB from XXXXXX where INVOICE_NUMB NOT IN (SELECT REC_INVOICE_NUMB from yyyyyy where some conditions----

    please suggest me how can i achieve this ,

    thanks in advance

    Venkat Kolli

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Redesign your database. You need to normalize your tables, then you won't have this problem any more

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    By all means.

    As of the question itself, regular expressions might help:
    Code:
    SQL> with test as
      2    (select '123456,567890,123765' col from dual)
      3  select regexp_substr(col, '[^,]+', 1, rownum) result
      4  from test
      5  connect by level <= length(regexp_replace(col, '[^,]+')) + 1;
    
    RESULT
    ------------------------------------------------------------------
    123456
    567890
    123765
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •