Home » SQL & PL/SQL » SQL & PL/SQL » split name
split name [message #665692] |
Fri, 15 September 2017 08:49 |
|
suji6281
Messages: 145 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 |
cookiemonster
Messages: 13943 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 #665699 is a reply to message #665695] |
Fri, 15 September 2017 12:33 |
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 |
|
suji6281
Messages: 145 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 |
|
Littlefoot
Messages: 21818 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 |
|
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 |
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 |
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)
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:48:20 CDT 2024
|