Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: Sorry guys I have to reask.

    I think I posted this here or in the Access forum, but can't find it. Anyhow in an excel sheet names were entered as Last, First, Middle Int in a single field. I need to split that up into a field for each. Any ways i can cut them up? Like a trim to first comma, then the second. Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't use Access, but the principle should be the same: you'll have to use a SUBSTRING function combined with a function that'll calculate position of delimiters (comma, space or whatever) and (eventually) length of a string.

    In Oracle, those functions are:
    SUBSTR(string, starting position, length)
    INSTR(string, substring you're looking for, starting position, occurrence)
    LENGTH(string)

    So, for example:
    Code:
    CREATE TABLE a (dugo varchar2(20));
    INSERT INTO a VALUES ('John M. Doe');
    First (John), Middle (M.) and Last (Doe) name would then be:
    Code:
    SELECT SUBSTR(dugo, 1, INSTR(dugo, ' ', 1, 1) - 1) name FROM a;
    
    SELECT SUBSTR(dugo, INSTR(dugo, ' ', 1, 1), 
                  INSTR(dugo, ' ', 1, 2) - INSTR(dugo, ' ', 1, 1)
    			 ) middle FROM a;
      
    SELECT SUBSTR(dugo, INSTR(dugo, ' ', 1, 2), LENGTH(dugo)) last FROM a;

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Thanks! But can anybody chime in how I can pop that into Access or excel?
    Ryan
    My Blog

  4. #4
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    I found this, but dont know how to apply it.
    Ryan
    My Blog

Posting Permissions

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