Sorry guys I have to reask.

rguy84 · #1 (**permalink**) 07-15-04, 18:55

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.

Littlefoot · #2 (**permalink**) 07-16-04, 03:31

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;

rguy84 · #3 (**permalink**) 07-16-04, 11:20

Thanks! But can anybody chime in how I can pop that into Access or excel?

rguy84 · #4 (**permalink**) 07-16-04, 14:02

I found this, but dont know how to apply it.

Internet Services

Web Development

Digital Marketing

Consumer Tech