Home » SQL & PL/SQL » SQL & PL/SQL » seperate the First Name and Last Name
seperate the First Name and Last Name [message #175691] Sun, 04 June 2006 21:55 Go to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Hi Gurus,

I need to write a SQl to distingusih the first name and last name from spreadsheet.

Like
greg smith.

I want to write teh script that can pick First name and last name seperately.

Thanks in advance.

Rupi
Re: seperate the First Name and Last Name [message #175692 is a reply to message #175691] Sun, 04 June 2006 22:00 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Well, if first name always comes first, and last name always comes last, and they are always separated by one space, then:
SQL> SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
SQL> DECLARE
  2      l_name  VARCHAR2(30) := 'greg smith';
  3  BEGIN
  4      DBMS_OUTPUT.PUT_LINE('First name = ' || SUBSTR(l_name,1,INSTR(l_name,' ') - 1));
  5      DBMS_OUTPUT.PUT_LINE('Last name = ' || SUBSTR(l_name,INSTR(l_name,' ') + 1));
  6  END;
  7  /
First name = greg
Last name = smith
 
PL/SQL procedure successfully completed.
 
SQL>
Re: seperate the First Name and Last Name [message #175694 is a reply to message #175692] Sun, 04 June 2006 22:27 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
This works.
Thanks for your help ART.
please keep this up.

Warm regards
Rupi
Re: seperate the First Name and Last Name [message #175695 is a reply to message #175694] Sun, 04 June 2006 22:30 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Hi metzer,

What if i want to run this query on my spreadsheet having one coloumn for firstandlast name. How to pass the spreadsheet for this query.

Thanks in advance.

Rupi
Re: seperate the First Name and Last Name [message #175710 is a reply to message #175695] Mon, 05 June 2006 01:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Spreadsheet" - like MS Excel spreadsheet, for example? What did you do with the first "spreadsheet" (the one from post #1) between 05:00 and 05:27 in order to make Art's suggestion work, and why doesn't it work now?

I'm asking that because it seems that the real question should be "how to import spreadsheet into Oracle database?" Is it? Or am I completely wrong about it?
Re: seperate the First Name and Last Name [message #175713 is a reply to message #175710] Mon, 05 June 2006 02:00 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Yes real question is "how to import spreadsheet into Oracle database?"
But Art suggested how to write code that works if i have to pass one person name with last name.
But customer is provided MS Excel spreadsheet with 1000 names. I want to import that into Service Desk database.
So i want to know how to use that code from extracting the data to seperate fields for First name and Last name.
Thanks for you help.

Regards
Rupinder
Re: seperate the First Name and Last Name [message #175722 is a reply to message #175713] Mon, 05 June 2006 02:37 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are many topics on this forum that cover importing process from spreadsheet into Oracle; search for them.

Shortly: you'd have to save your spreadsheet as a CSV file and either load it into Oracle using SQL*Loader or use it as an external table. It is possible to do so using Access, ODBC etc., but I think that a simple CSV file used as an external table is the simplest solution (of course, if your Oracle DB version supports this feature).

What will you do once you get first name and last name, separated? Insert them into another Oracle table? If so, no problem: Art's procedure already gave you those values; you'd only have to perform an INSERT operation:

INSERT INTO some_table (first_name, last_name)
VALUES
(
SUBSTR(l_name, 1, INSTR(l_name, ' ') - 1),
SUBSTR(l_name, INSTR(l_name, ' ') + 1)
)
Re: seperate the First Name and Last Name [message #175885 is a reply to message #175722] Mon, 05 June 2006 19:22 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Hi Littlefoot,

Thanks for your suggestion. I will follow as you suggest and thanks to Art too.

kind Regards
Rupi
Re: seperate the First Name and Last Name [message #196483 is a reply to message #175691] Thu, 05 October 2006 09:39 Go to previous messageGo to next message
catpurr
Messages: 11
Registered: November 2005
Junior Member
How to use sql loader to load them in separet columns?
Re: seperate the First Name and Last Name [message #196537 is a reply to message #196483] Thu, 05 October 2006 19:19 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
may be this helps you

1. create test.dat under /tmp/xxx on database

Firstname.lastname

2. create a table under dsi_dpipe schema

CREATE TABLE test
( First_NAME VARCHAR2(64),
last_NAME VARCHAR2(64),


3. create a control file on database

LOAD DATA
INFILE '/tmp/xxx/test.dat'
INTO TABLE test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(first_NAME, lastname)

Save the file as test.ctl in your /tmp/xxx

4. run sqlldr

sqlldr userid=xyz@dbsid control=/tmp/xxx/test.ctl log=/tmp/test.log

5. update the table

Update Table
Select -----


Cheers
rupi
Re: seperate the First Name and Last Name [message #196576 is a reply to message #196537] Fri, 06 October 2006 00:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If your database version supports it, use of external table could simplify your task.
Re: seperate the First Name and Last Name [message #196581 is a reply to message #196576] Fri, 06 October 2006 01:00 Go to previous message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
I agree with littlefoot.
Previous Topic: query result between 2 constaint time BUT vary Date
Next Topic: Help checking constraints
Goto Forum:
  


Current Time: Sat Dec 10 18:51:46 CST 2016

Total time taken to generate the page: 0.08084 seconds