Forum Stats

  • 3,767,862 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

Name field in 3 Columns

User_2FAC8
User_2FAC8 Member Posts: 7 Blue Ribbon

Hello, I have a Name Column. I am trying to separate the NAME column in three separate columns...FirstName, MiddleName, LastName. I'm able to extract the first name but get the Middle or Last Name. If the person has no middle name then the MiddleName columns should return be null or blank.

I have attached a sample data in xlsx format

Here's the code I started.

SELECT DISTINCT VENDOR_NO, NAME, initcap(regexp_substr(NAME, '[^ ]+')) AS FIRSTNAME,   

- Ltrim(SubStr(name, 1, Is null(Nullif(CHARINDEX(' ', name), 0), 1000))) AS FirstName--   SUBSTR(NAME,INSTR(NAME,' ',1,1)+1,INSTR(NAME,' ' ,1,1)-2 ) AS MN  ,         SSN

FROM BCCC_VENDORLISTING

WHERE SSN = 'Y'


Thank you in advance.

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi, @User_2FAC8

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. Post everything right in this space; not everyone can or will open attachments.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    CHARINDEX(' ', name)

    There is no built-in CHARINDEX function in Oracle. If you're using any user-defined functions, post CREATE FUNCTION statements for them.

  • User_2FAC8
    User_2FAC8 Member Posts: 7 Blue Ribbon

    Ok, I will try to post again. I am using Oracle 19 (Oracle SQL Developer). Below is sample table data. What I would like to do is split out the NAME column in three separate columns. FIRST_NAME, MIDDLE_NAME, LAST_NAME. I tried. I was able to substr the first name but having diffculty with the MIDDLE and LAST NAME.

     SELECT NAME, initcap(regexp_substr(NAME, '[^ ]+')) AS FIRSTNAME         

    FROM TESTING;


    CREATE TABLE TESTING(

      NAME VARCHAR2(128),

      first_name VARCHAR2(50) NOT NULL,

      Middle_name varchar2(50) NULL,

      last_name VARCHAR2(50) NOT NULL

    );


    /Expected Results are first_name, middle_name, last_name columns below/

    INSERT INTO TESTING

    (NAME, first_name, Middle_name, last_name)

     values

     ('TIMOTHY S MALLARD', 'Timothy', 'S', 'Mallard');

    INSERT INTO TESTING

    (NAME, first_name, Middle_name, last_name)

     values

     ('STACEY VALENTINE ROYSTER', 'Stacey', 'Valentine','Royster');

     INSERT INTO TESTING

    (NAME, first_name, Middle_name, last_name)

     values 

     ('MICHELLE MULLER','Michelle','','Muller');

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi,

    So, the first_name is everything upto the first space, the last_name is everything after the last space, and the middle name is everything between the first and last spaces (if anything). Start by finding the position of the first space (f_pos_) and the position of the last space (l_pos). Once you know those positions, SUBSTR can get the parts you want. Here's one way to do that:

    WITH  got_pos  AS
    (
    	SELECT  name
    	,	INSTR (name, ' ')	AS f_pos
    	,	INSTR (name, ' ', -1)	AS l_pos
    	FROM	testing
    --	WHERE	... -- if needed
    )
    SELECT   name
    ,	 INITCAP (SUBSTR (name, 1, f_pos - 1))			  AS first_name
    ,	 INITCAP (SUBSTR (name, f_pos + 1, l_pos - (f_pos + 1)))  AS middle_name
    ,	 INITCAP (SUBSTR (name, l_pos + 1)) 	  	   	  AS last_name
    FROM	 got_pos
    ORDER BY name
    ;
    

    There are people with only one name. If there are no spaces in the full name, then the query above considers the whole name as being the last_name, and both first_name and middle_name are NULL.

  • User_H3J7U
    User_H3J7U Member Posts: 630 Silver Trophy

    There are people with only one name.

    There are people with four words:

    • Joseph Robinette Biden Jr.
    • Barack Hussein Obama II
    • George Herbert Walker Bush
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,581 Red Diamond
    edited Jun 10, 2021 7:01AM

    In addition, 2 word surnames are common in some parts of the world - such as De Graaf.

  • User_2FAC8
    User_2FAC8 Member Posts: 7 Blue Ribbon

    Thank you. I will test this out.

  • ascheffer
    ascheffer Member Posts: 1,926 Gold Trophy
    edited Jun 10, 2021 10:28AM

    With a requirement like this OP is probably not from the Netherlands.

    Here we have no middle name, but some people have a "double first name", like "Kees Willem" or "Johan Pieter".

    And last names can have more words, 3 parts are not that uncommon, like "van der Voort". And in some systems that is stored as "Voort, van der" 😎