CODEWITHSUNDEEP
sqloracle-database
ecooper10
ecooper10

Reputation: 107

separating full name with oracle

I need to figure out how to turn my "name" field to show as separate "first name" and "last name" fields on my query. Here is my data:

set linesize 250

drop table homework1data;

CREATE TABLE homework1data (
  name VARCHAR2(30),

  address VARCHAR2(30),

  location VARCHAR2(30),

  zip VARCHAR2(10)
  );

INSERT INTO Homework1Data(Name, Address, Location, Zip) 
VALUES('Ferguson, Shawn M.', '1940 Fountainview Court', 'Reynoldsburg, Ohio', '43068');

INSERT INTO Homework1Data(Name, Address, Location, Zip) 
VALUES('Phillips, George', '19 Pleasant St.', 'Columbus, OH', '43231');

INSERT INTO Homework1Data(Name, Address, Location, Zip) 
VALUES('Thompson, Mary', '200 E. Main St.', 'Columbus, Oh', '43215');

INSERT INTO Homework1Data(Name, Address, Location, Zip) 
VALUES('Swatson, Robert', '584 Yellowstone Dr.', 'Westerville, OH', '43081');

INSERT INTO Homework1Data(Name, Address, Location, Zip) 
VALUES('Banks, Heather T.', '19 Pleasant St.', 'Columbus, Ohio', '43231');

I think I need to use a select statement with LTRIM and RTRIM but unsure of how to do so

UPDATE:

I did this to break my fields up:

SELECT substr(name, 1, instr(name, ',')) "Last Name",substr(name, instr(name, ' ', 1)) "First Name", address,substr(location, 1, instr(location, ',')) "City",substr(location, instr(location, ' ', 1)) "State", zip 
FROM homework1data
ORDER BY zip, "Last Name" , "First Name";

but now my query report has too much space in between each column so that you have to use to scroll bar to see the last field. Is this adjustable? Tried changing line and pagesize but was unsuccessful.

Also, just realized that for some reason all of my rows have duplicated (can be seen in screenshot), giving me doubles of everything. Not sure how or why this happened?

report screenshot

Upvotes: 1

Views: 3175

Answers (2)

Dmitry
Dmitry

Reputation: 2806

In the Oracle SQL's you can use such a powerful tool as a regular expression:

SELECT 
    REGEXP_SUBSTR (name, '^\w+') as first_name,
    REGEXP_SUBSTR (name, '\w+$') as last_name
FROM
    homework1data

Upvotes: 1

Prakash Dharuman
Prakash Dharuman

Reputation: 37

Try this:

SELECT
    SUBSTRING_INDEX(name, ',', 1) AS FirstName,
    SUBSTRING_INDEX(name, ',', -1) AS LastName,
    address,
    SUBSTRING_INDEX(location, ',', 1) AS State,
    SUBSTRING_INDEX(location, ',', -1) AS City,
    zip
FROM homework1data

Upvotes: 0

Related Questions