Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unanswered: Splitting A Record

    Hi,
    I have a table with 2 columns

    Id,Loc
    1,NJ@NY@PA@CA
    2,CT
    3,CA@VA



    I want the output to be
    1,nj
    1,ny
    1,pa
    1,ca
    2,ct
    3,ca
    3,va

    Not sure how to split the data.@ is delimter in data.Record count can change and there can be N number of delimiters in data

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Have you bothered to search, or would you like other people to do it for you? There's another thread on the subject on this same page.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another point which you should keep in mind would be
    to provide exact descriptions and sample data covering as many cases as possible for your requirements.

    For example:
    1) Your output sample were lower cases while input were upper cases.
    2) All element length in your sample were 2.

    If those were all possible cases, the following sample would be enough

    Example 1: Up to 100 elements.
    Note: this might not be your expected solution. But, returned exactly same result you wanted.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      t(id , loc) AS (
    VALUES
      ( 1 , 'NJ@NY@PA@CA' )
    , ( 2 , 'CT'          )
    , ( 3 , 'CA@VA'       )
    )
    SELECT VARCHAR(id) || ',' ||
           LOWER( SUBSTR(loc , k * 3 - 2 , 2) ) AS output
     FROM  t
     INNER JOIN (
           SELECT k1 + k2 AS k
            FROM (VALUES     1, 2, 3, 4, 5, 6, 7, 8, 9,10 ) k(k1)
               , (VALUES  0,10,20,30,40,50,60,70,80,90    ) k(k2)
           ) k
       ON  k <= LENGTH(loc) / 3 + 1
     ORDER BY
           id , k
    ;
    ------------------------------------------------------------------------------
    
    OUTPUT        
    --------------
    1,nj          
    1,ny          
    1,pa          
    1,ca          
    2,ct          
    3,ca          
    3,va          
    
      7 record(s) selected.
    Last edited by tonkuma; 02-03-12 at 21:06.

Posting Permissions

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