If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Split a name into portions

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: May 2006
Posts: 46
Split a name into portions

Hi,

I have a name column full_name which has values like this. eg.Hartwells Manchester. Through a simple select statement, I need to split into
First Name : Hartwells and Last Name : Manchester.

How do I achieve this. Please help me.

Thanks
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
you achieve this by using string functions, which, sadly, are different from one database to another, and there's not much chance that standard SQL will be able to help you

if you would kindly mention which database you're using, we can move your thread into the appropriate forum
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: May 2006
Posts: 46
Hi,

I am working in Oracle 9i database. I already tried with substr and instr functions.

Can u plz. give me some examples on this.

Thanks
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jul 2005
Location: jakarta
Posts: 21
If you use oracle:

i created a table called dbforum with a column called names (varchar2).

insert some values.

now, assuming the only thing seperating first name and last name is a space, you can simply do this:

select
substr(names, 1, string_pos -1) as First_name,
substr(names, string_pos, length(names)) as last_name
from (
select
t.names,
instr(t.names, ' ') as string_pos
from dbforum t );

all this is assuming youre using oracle sql and that the separator is a space. if the separator is not a space, youre going to have to do alot of decoding.

hope this helps.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: May 2006
Posts: 46
Split a name into portions

Hello,

Thanks a lot, thats really worked fine.

Thanks again.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: May 2006
Posts: 6
This is a classic problem

You ask an excellent question. Unfortunately there aren't any really good simple answers. Some people resort to user defined functions. It certainly gives you full control over the problem. You can even resort to RegExp (Regular Expressions), yes MS Access does support a fairly full version. Others complain that this approach is slow or a drain on the system. ALL string processing is a DRAIN on a system. A very old solution used by the biggest and most sophisticated Mail Order companies use special coding when they enter names. Then they use custom tuned functions for searching and displaying the specially encoded field. What they do is enter the name in proper sort order, such as last name [dlm] first name. * is a good value for [dlm] but it could be problematic with RegExp. -enjoy!
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: May 2006
Posts: 6
Simple solutions work only on simple data

How does your routine work on:
"John Jones, Jr." ? Do you enter it "Jones, Jr., John" ? Which comma do you break it at?
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: May 2006
Posts: 46
Hi,

If the same is separated with comma or a space, split that into first_nm,middle_nm and last_nm. how I use the delimiter here in this situation.
Mostly the names are separated by spaces.

can u help me

thanks
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jul 2005
Location: jakarta
Posts: 21
You dont need to use regular expressions. well, i tend to shy away from regex because it complicates life rather than make it easier. anyway, that said, your question, although the question is simple, the answer is capable of being pretty complicated. you will be doing lots of exctractions and cleaning. i think most of the question can be answered by using simple INSTR function (BUT again, this all depends on the content of the table, eg, if the only seperator is only space or comma it wouldnt be too bad, remember i asked you earlier and assumed it was only space. but in reality the content can be anything, so you will need to extract all the anomaly in an anomaly table...etc).

sorry, but i dont have the time to write the script for you but i will tell you that your PL-SQL script will most probably have a few tables for extraction purposes - and the extraction will use INSTR and probable DECODE (or better yet CASE WHEN) functions.

good luck
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jun 2009
Posts: 1
Cool RE: Split Name

Quote:
Originally Posted by duul
If you use oracle:

i created a table called dbforum with a column called names (varchar2).

insert some values.

now, assuming the only thing seperating first name and last name is a space, you can simply do this:

select
substr(names, 1, string_pos -1) as First_name,
substr(names, string_pos, length(names)) as last_name
from (
select
t.names,
instr(t.names, ' ') as string_pos
from dbforum t );

all this is assuming youre using oracle sql and that the separator is a space. if the separator is not a space, youre going to have to do alot of decoding.

hope this helps.
This one did the trick. You are awesome!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On