Results 1 to 10 of 10
  1. #1
    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

  2. #2
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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

  4. #4
    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.

  5. #5
    Join Date
    May 2006
    Posts
    46

    Split a name into portions

    Hello,

    Thanks a lot, thats really worked fine.

    Thanks again.

  6. #6
    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!

  7. #7
    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?

  8. #8
    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

  9. #9
    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

  10. #10
    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!

Posting Permissions

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