Home » SQL & PL/SQL » SQL & PL/SQL » split name
split name [message #665692] Fri, 15 September 2017 08:49 Go to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Team,

I would like to split the name field as First Name, Middle Name & Last Name based on the Spaces in the Name field.
Please help me with SQL Query.

Case 1: If Name has 2 spaces then it should be 3 parts
Case 2: If Name has 1 space, it should split as First Name & Last Name
Case 3: If Name has no space, it should be First Name
CREATE TABLE EMP_TBL (
    EMPLID Varchar(11),
    Name varchar(55),
    Address varchar (30),
    PHONE varchar(12));
INSERT INTO EMP_TBL (EMPLID, Name, Address, PHONE) VALUES ('457891', 'John Millar A.', 'USA', '473-781-1111'); 
INSERT INTO EMP_TBL (EMPLID, Name, Address, PHONE) VALUES ('254784', 'Gray Tony', 'USA', '473-781-2222'); 
INSERT INTO EMP_TBL (EMPLID, Name, Address, PHONE) VALUES ('658975', 'Clare', 'USA', '473-781-3333'); 
Output should be as below:
EMPLID    FirstName     MiddleName     LastName   Address
457891	    John         Millar          A.        USA 
254784	    Gray                        Tony       USA        
658975	    Clare                                  USA      

Thanks You.

Regards
Sekhar



Re: split name [message #665694 is a reply to message #665692] Fri, 15 September 2017 09:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you sure about those rules?
They don't seem to match your data.
I would assume A. is a middle name
And Tony is a first name.
Re: split name [message #665695 is a reply to message #665694] Fri, 15 September 2017 09:23 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
I'm sure about these rules.
Word before 1st Space should be First Name, word after 2nd Space should be Last name and the word between 2 spaces should be middle name.
Re: split name [message #665699 is a reply to message #665695] Fri, 15 September 2017 12:33 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Bad rules.
What about first name of Mary Jo, last name of Llewelyn Davies?
You can't have a general rule like you propose. This is a common dilemma asked many times.
Re: split name [message #665703 is a reply to message #665699] Fri, 15 September 2017 14:33 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Cookie & Joy,

please don't look at the given Names, its a sample data only. Assume there should be maximum of 2 spaces in the name and wanted it should be divided based on the space. Characters before 1st space should be First Name, Characters after 2nd Space should be Last Name and Characters between these 2 spaces should be Middle Name.
I have tried with below logic but output as below.

SELECT EMPLID, 
  NVL (TRIM (CASE WHEN INSTR (NAME, ' ', 1) - 1 = 0 THEN ' ' ELSE SUBSTR (NAME, 1, INSTR (NAME, ' ', 1) - 1) END)),
NVL (TRIM (CASE WHEN INSTR (NAME, ' ', 1, 2) < > 0 THEN (SUBSTR (NAME, INSTR (NAME, ' ', 1) + 1,
(INSTR (NAME, ' ', 1, 2) - INSTR (NAME, ' ', 1))) ELSE SUBSTR (NAME, INSTR (NAME, ' ', 1) + 1, LENTH (NAME) - INSTR (NAME, ' ', 1) + 1) END)),
NVL (TRIM (CASE (WHEN INSTR (NAME, ' ', 1, 2) = 0 THEN ' ' ELSE SUBSTR (NAME, INSTR (NAME, ' ', 1, 2), (LENGTH (NAME) - INSTR (NAME, ' ', 1, 2)) + 1) END)),
Address FROM EMP_TBL

output:
EMPLID    FirstName     MiddleName     LastName   Address
457891	    John         Millar          A.        USA 
254784	    Gray         Tony                      USA        
658975	                 Clare                     USA

But I need output something different as mentioned in my 1st Post. Please help me here.

Re: split name [message #665712 is a reply to message #665703] Sat, 16 September 2017 05:46 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option which works on data you provided. However, depending on variations, it might (or might not) produce the result you expect. Just as you've already been told, there are too many "ifs" there. Anyway, here you are:

SQL> select
  2    name,
  3    substr(name, 1, decode(instr(name, ' '), 0, length(name) + 1, instr(name, ' ')) - 1) first_name,
  4    --
  5    case when regexp_count(name, ' ') = 2 then
  6              substr(name, instr(name, ' ') + 1,
  7                           instr(name, ' ', -1) - instr(name, ' ') - 1
  8                    )
  9         end middle_name,
 10    --
 11    case when regexp_count(name, ' ') in (1, 2) then
 12               substr(name, instr(name, ' ', -1) + 1, length(name))
 13         end last_name
 14  from emp_tbl;

NAME                 FIRST_NAME MIDDLE_NAM LAST_NAME
-------------------- ---------- ---------- ----------
John Millar A.       John       Millar     A.
Gray Tony            Gray                  Tony
Clare                Clare
Re: split name [message #665717 is a reply to message #665692] Mon, 18 September 2017 01:12 Go to previous messageGo to next message
azamkhan
Messages: 557
Registered: August 2005
Senior Member
I hope the following will resolve your issue.

Select (Case
When REGEXP_COUNT(Ltrim(Rtrim('&Full_Name')), ' ') In (1, 2) Then
SubStr(Ltrim(Rtrim('&Full_Name')), 1, InStr(Ltrim(Rtrim('&Full_Name')), ' ', 1, 1)-1)
When REGEXP_COUNT(Ltrim(Rtrim('&Full_Name')), ' ') = 0 Then
'&Full_Name'
End) N1,
(Case
When REGEXP_COUNT(Ltrim(Rtrim('&Full_Name')), ' ') = 1 Then
SubStr(Ltrim(Rtrim('&Full_Name')), InStr(Ltrim(Rtrim('&Full_Name')), ' ', 1, 1)+1)

When REGEXP_COUNT(Ltrim(Rtrim('&Full_Name')), ' ') = 2 Then
SubStr(SubStr(Ltrim(Rtrim('&Full_Name')), InStr(Ltrim(Rtrim('&Full_Name')), ' ', 1, 1)+1),
1, InStr(SubStr(Ltrim(Rtrim('&Full_Name')), InStr(Ltrim(Rtrim('&Full_Name')), ' ', 1, 1)+1), ' ', 1, 1)-1)

End) N2,
(Case
When REGEXP_COUNT(Ltrim(Rtrim('&Full_Name')), ' ') = 2 Then

SubStr(SubStr(Ltrim(Rtrim('&Full_Name')), InStr(Ltrim(Rtrim('&Full_Name')), ' ', 1, 1)+1),
InStr(SubStr(Ltrim(Rtrim('&Full_Name')), InStr(Ltrim(Rtrim('&Full_Name')), ' ', 1, 1)+1), ' ', 1)+1
)
End) N3
From Dual;

[Updated on: Mon, 18 September 2017 05:51]

Report message to a moderator

Re: split name [message #665719 is a reply to message #665717] Mon, 18 September 2017 07:48 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Aside from being unreadable, I ran your code with a first and last name. It put the last name in the middle name column.
Re: split name [message #665722 is a reply to message #665719] Mon, 18 September 2017 09:01 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
SELECT Emplid,
       Pos1 Firstname,
       CASE WHEN Pos3 IS NULL THEN NULL ELSE Pos2 END Middlename,
       CASE WHEN Pos3 IS NULL THEN Pos2 ELSE Pos3 END Lastname,
       Address
  FROM (SELECT A.Emplid,
               A.Address,
               REGEXP_SUBSTR (A.Name,
                              '[^ ]+',
                              1,
                              1)
                   Pos1,
               REGEXP_SUBSTR (A.Name,
                              '[^ ]+',
                              1,
                              2)
                   Pos2,
               REGEXP_SUBSTR (A.Name,
                              '[^ ]+',
                              1,
                              3)
                   Pos3
          FROM Emp_tbl A)
Re: split name [message #665744 is a reply to message #665712] Tue, 19 September 2017 08:33 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Thank you Littlefoot. Given code was working fine.
Re: split name [message #665745 is a reply to message #665744] Tue, 19 September 2017 08:43 Go to previous message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Thanks Bill. Tried with your solution and it's working fine.
Previous Topic: finding status
Next Topic: extract numbers from a varchar2 upto non-numeric
Goto Forum:
  


Current Time: Fri Mar 29 05:12:06 CDT 2024