Split Name into Last name, First Name, Middle Name in SQL
1
I have a record below. I want to split the name into Lname, Fname, Mname
Name
John, David Handsome
Here is my query. I was able to get Lname but not Fname and Mname yet.
SELECT
NAME,
LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME
FROM TABLE
My desired output would be:
Name Lname Fname Mname
John, David Handsome John Daivd Handsome
sql
sql-server edited Nov 10 at 14:27
halfer
14.3k758109
asked Nov 21 '17 at 18:48
joe
52062135
|
show 4 more comments
1
I have a record below. I want to split the name into Lname, Fname, Mname
Name
John, David Handsome
Here is my query. I was able to get Lname but not Fname and Mname yet.
SELECT
NAME,
LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME
FROM TABLE
My desired output would be:
Name Lname Fname Mname
John, David Handsome John Daivd Handsome
sql
sql-server edited Nov 10 at 14:27
halfer
14.3k758109
asked Nov 21 '17 at 18:48
joe
52062135
3
what DBMS are you using
– Radim Bača
Nov 21 '17 at 18:49
2
What if it is a Latin name like :Iván De Luca Di Nataleor a French:Isaac De la CroixorMaurits-jan Kuipers op den Kollenstaart? How do you get the mid name?
– danieltakeshi
Nov 21 '17 at 18:55
very good question @ Daniel
– joe
Nov 21 '17 at 19:02
I am using SQL sever. The name field is a very common name. No Latin or French names, ect, ect. Thanks
– joe
Nov 21 '17 at 19:24
which version of SQL?
– maSTAShuFu
Nov 21 '17 at 19:37
|
show 4 more comments
1
1
1
1
I have a record below. I want to split the name into Lname, Fname, Mname
Name
John, David Handsome
Here is my query. I was able to get Lname but not Fname and Mname yet.
SELECT
NAME,
LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME
FROM TABLE
My desired output would be:
Name Lname Fname Mname
John, David Handsome John Daivd Handsome
sql
sql-server edited Nov 10 at 14:27
halfer
14.3k758109
asked Nov 21 '17 at 18:48
joe
52062135
I have a record below. I want to split the name into Lname, Fname, Mname
Name
John, David Handsome
Here is my query. I was able to get Lname but not Fname and Mname yet.
SELECT
NAME,
LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME
FROM TABLE
My desired output would be:
Name Lname Fname Mname
John, David Handsome John Daivd Handsome
sql
sql-server sql
sql-server edited Nov 10 at 14:27
halfer
14.3k758109
asked Nov 21 '17 at 18:48
joe
52062135
edited Nov 10 at 14:27
halfer
14.3k758109
asked Nov 21 '17 at 18:48
joe
52062135
edited Nov 10 at 14:27
halfer
14.3k758109
edited Nov 10 at 14:27
halfer
14.3k758109
edited Nov 10 at 14:27
halfer
14.3k758109
14.3k758109
asked Nov 21 '17 at 18:48
joe
52062135
asked Nov 21 '17 at 18:48
joe
52062135
asked Nov 21 '17 at 18:48
joe
52062135
52062135
3
what DBMS are you using
– Radim Bača
Nov 21 '17 at 18:49
2
What if it is a Latin name like :Iván De Luca Di Nataleor a French:Isaac De la CroixorMaurits-jan Kuipers op den Kollenstaart? How do you get the mid name?
– danieltakeshi
Nov 21 '17 at 18:55
very good question @ Daniel
– joe
Nov 21 '17 at 19:02
I am using SQL sever. The name field is a very common name. No Latin or French names, ect, ect. Thanks
– joe
Nov 21 '17 at 19:24
which version of SQL?
– maSTAShuFu
Nov 21 '17 at 19:37
|
show 4 more comments
3
what DBMS are you using
– Radim Bača
Nov 21 '17 at 18:49
2
What if it is a Latin name like :Iván De Luca Di Nataleor a French:Isaac De la CroixorMaurits-jan Kuipers op den Kollenstaart? How do you get the mid name?
– danieltakeshi
Nov 21 '17 at 18:55
very good question @ Daniel
– joe
Nov 21 '17 at 19:02
I am using SQL sever. The name field is a very common name. No Latin or French names, ect, ect. Thanks
– joe
Nov 21 '17 at 19:24
which version of SQL?
– maSTAShuFu
Nov 21 '17 at 19:37
3
3
what DBMS are you using
– Radim Bača
Nov 21 '17 at 18:49
what DBMS are you using
– Radim Bača
Nov 21 '17 at 18:49
2
2
What if it is a Latin name like :
Iván De Luca Di Natale or a French: Isaac De la Croix or Maurits-jan Kuipers op den Kollenstaart? How do you get the mid name?– danieltakeshi
Nov 21 '17 at 18:55
What if it is a Latin name like :
Iván De Luca Di Natale or a French: Isaac De la Croix or Maurits-jan Kuipers op den Kollenstaart? How do you get the mid name?– danieltakeshi
Nov 21 '17 at 18:55
very good question @ Daniel
– joe
Nov 21 '17 at 19:02
very good question @ Daniel
– joe
Nov 21 '17 at 19:02
I am using SQL sever. The name field is a very common name. No Latin or French names, ect, ect. Thanks
– joe
Nov 21 '17 at 19:24
I am using SQL sever. The name field is a very common name. No Latin or French names, ect, ect. Thanks
– joe
Nov 21 '17 at 19:24
which version of SQL?
– maSTAShuFu
Nov 21 '17 at 19:37
which version of SQL?
– maSTAShuFu
Nov 21 '17 at 19:37
|
show 4 more comments
2 Answers
2
active
oldest
votes
0
If the format of name is gonna remain the same then use below :
select name,LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME, regexp_substr(name,'[^ ]+',1,2) as fname,regexp_substr(name,'[^ ]+',1,3) mname from table;
answered Nov 21 '17 at 19:15
Nikhil Shetkar
36619
add a comment |
0
You can do it like this;
SELECT
LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME,
LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1) AS FNAME,
REPLACE(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1),'') AS MNAME
It comes little confusing because of nested replace and charindex functions. But if you focus on them closely, logic is quite simple.
answered Nov 21 '17 at 20:10
lucky
9,83541227
You're welcome.
– lucky
Nov 21 '17 at 20:37
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f47420513%2fsplit-name-into-last-name-first-name-middle-name-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
If the format of name is gonna remain the same then use below :
select name,LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME, regexp_substr(name,'[^ ]+',1,2) as fname,regexp_substr(name,'[^ ]+',1,3) mname from table;
answered Nov 21 '17 at 19:15
Nikhil Shetkar
36619
add a comment |
0
If the format of name is gonna remain the same then use below :
select name,LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME, regexp_substr(name,'[^ ]+',1,2) as fname,regexp_substr(name,'[^ ]+',1,3) mname from table;
answered Nov 21 '17 at 19:15
Nikhil Shetkar
36619
add a comment |
0
0
0
If the format of name is gonna remain the same then use below :
select name,LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME, regexp_substr(name,'[^ ]+',1,2) as fname,regexp_substr(name,'[^ ]+',1,3) mname from table;
answered Nov 21 '17 at 19:15
Nikhil Shetkar
36619
If the format of name is gonna remain the same then use below :
select name,LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME, regexp_substr(name,'[^ ]+',1,2) as fname,regexp_substr(name,'[^ ]+',1,3) mname from table;
answered Nov 21 '17 at 19:15
Nikhil Shetkar
36619
answered Nov 21 '17 at 19:15
Nikhil Shetkar
36619
answered Nov 21 '17 at 19:15
Nikhil Shetkar
36619
answered Nov 21 '17 at 19:15
Nikhil Shetkar
36619
36619
add a comment |
add a comment |
0
You can do it like this;
SELECT
LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME,
LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1) AS FNAME,
REPLACE(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1),'') AS MNAME
It comes little confusing because of nested replace and charindex functions. But if you focus on them closely, logic is quite simple.
answered Nov 21 '17 at 20:10
lucky
9,83541227
You're welcome.
– lucky
Nov 21 '17 at 20:37
add a comment |
0
You can do it like this;
SELECT
LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME,
LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1) AS FNAME,
REPLACE(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1),'') AS MNAME
It comes little confusing because of nested replace and charindex functions. But if you focus on them closely, logic is quite simple.
answered Nov 21 '17 at 20:10
lucky
9,83541227
You're welcome.
– lucky
Nov 21 '17 at 20:37
add a comment |
0
0
0
You can do it like this;
SELECT
LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME,
LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1) AS FNAME,
REPLACE(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1),'') AS MNAME
It comes little confusing because of nested replace and charindex functions. But if you focus on them closely, logic is quite simple.
answered Nov 21 '17 at 20:10
lucky
9,83541227
You can do it like this;
SELECT
LEFT(NAME,CHARINDEX(',',NAME)-1) AS LNAME,
LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1) AS FNAME,
REPLACE(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),LEFT(REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''),CHARINDEX(' ',REPLACE(NAME,LEFT(NAME,CHARINDEX(',',NAME)+1),''))-1),'') AS MNAME
It comes little confusing because of nested replace and charindex functions. But if you focus on them closely, logic is quite simple.
answered Nov 21 '17 at 20:10
lucky
9,83541227
answered Nov 21 '17 at 20:10
lucky
9,83541227
answered Nov 21 '17 at 20:10
lucky
9,83541227
answered Nov 21 '17 at 20:10
lucky
9,83541227
9,83541227
You're welcome.
– lucky
Nov 21 '17 at 20:37
add a comment |
You're welcome.
– lucky
Nov 21 '17 at 20:37
You're welcome.
– lucky
Nov 21 '17 at 20:37
You're welcome.
– lucky
Nov 21 '17 at 20:37
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f47420513%2fsplit-name-into-last-name-first-name-middle-name-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
what DBMS are you using
– Radim Bača
Nov 21 '17 at 18:49
2
What if it is a Latin name like :
Iván De Luca Di Nataleor a French:Isaac De la CroixorMaurits-jan Kuipers op den Kollenstaart? How do you get the mid name?– danieltakeshi
Nov 21 '17 at 18:55
very good question @ Daniel
– joe
Nov 21 '17 at 19:02
I am using SQL sever. The name field is a very common name. No Latin or French names, ect, ect. Thanks
– joe
Nov 21 '17 at 19:24
which version of SQL?
– maSTAShuFu
Nov 21 '17 at 19:37