March 30, 2015 at 7:36 am
Hi All,
I want to display full name column as FirstName and LastName.
Eg if Full Name is Abhas Jadhav then i want to convert it into FirstName- Abhas and LastName - Jadhav
i.e. Before Space is FirstName and After Space is LastName.
Thanks,
Abhas,
March 30, 2015 at 7:46 am
You need a splitter function: http://qa.sqlservercentral.com/articles/Tally+Table/72993/
-- Gianluca Sartori
March 30, 2015 at 7:47 am
What have you tried to do so far to make it work?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2015 at 8:14 am
What happens if you have full names with more than one space in them?
March 30, 2015 at 8:15 am
Luis, There will be only one space, 🙂
Thanks.
March 30, 2015 at 8:21 am
I completely agree that the set-based splitter is the right approach, but what if someone has a name like 'Georges St. Pierre' and you want to split it based on a space? How many names would he have?
I'm not trying to cause an argument here. I want to prevent trouble for you later.
March 30, 2015 at 8:28 am
For 2 columns, a splitter such as the Delimited8k would be too much.
This thread might give you an idea on how to do it.
http://qa.sqlservercentral.com/Forums/Topic1670877-391-1.aspx
March 30, 2015 at 8:34 am
Luis Cazares (3/30/2015)
For 2 columns, a splitter such as the Delimited8k would be too much.This thread might give you an idea on how to do it.
http://qa.sqlservercentral.com/Forums/Topic1670877-391-1.aspx
I like Scott's approach. Nice, simple and fast. As long as you leave enough space for the last name to be N characters, it's a great approach.
March 30, 2015 at 8:38 am
Hi Wanger, Thanks for suggestion.
The format will be same.such case will not happen.
SELECT LEFT(FullName, charindex(' ', FullName) - 1) i am able to find before space.
Lokking now for after space
Thanks
March 30, 2015 at 8:46 am
abhas (3/30/2015)
Hi Wanger, Thanks for suggestion.The format will be same.such case will not happen.
SELECT LEFT(FullName, charindex(' ', FullName) - 1) i am able to find before space.
Lokking now for after space
Thanks
Thank Luis for finding the perfect post. Here's the query you're looking for.
if OBJECT_ID('tempdb.dbo.#names', 'u') is not null drop table #names;
create table #names (
FullName varchar(100));
insert into #names(FullName)
values('Ed Wagner'),
('Georges St. Pierre'),
('John Smith');
select FirstName = SUBSTRING(fullname, 1, charindex(' ', fullname) - 1),
LastName = SUBSTRING(fullname, charindex(' ', fullname) + 1, 100)
from #names;
drop table #names;
Only the first space is the delimiter, so if you have a case where the person has a space in their first name, it won't come out right.
March 30, 2015 at 8:52 am
I'm just adding a safety net.
if OBJECT_ID('tempdb.dbo.#names', 'u') is not null drop table #names;
create table #names (
FullName varchar(100));
insert into #names(FullName)
values('Ed Wagner'),
('Georges St. Pierre'),
('John Smith'),
('Madonna');
select FirstName = SUBSTRING(fullname, 1, charindex(' ', fullname + ' ') - 1), --Add a trailing space
LastName = SUBSTRING(fullname, charindex(' ', fullname) + 1, 8000) --8000 will work with any length (except max)
from #names;
drop table #names;
March 31, 2015 at 12:58 pm
Luis Cazares (3/30/2015)
I'm just adding a safety net.
if OBJECT_ID('tempdb.dbo.#names', 'u') is not null drop table #names;
create table #names (
FullName varchar(100));
insert into #names(FullName)
values('Ed Wagner'),
('Georges St. Pierre'),
('Sarah Grace Smith'), -- Expected First: Sarah Grace Last: Smith
('John Smith'),
('Madonna');
select FirstName = SUBSTRING(fullname, 1, charindex(' ', fullname + ' ') - 1), --Add a trailing space
LastName = SUBSTRING(fullname, charindex(' ', fullname) + 1, 8000) --8000 will work with any length (except max)
from #names;
drop table #names;
But what if the first name has a space in the middle? The way the code is written it will return the wrong result in that case. Off-hand, I can't think of any method that would solve both problems.
March 31, 2015 at 1:07 pm
LightVader (3/31/2015)
But what if the first name has a space in the middle? The way the code is written it will return the wrong result in that case. Off-hand, I can't think of any method that would solve both problems.
That was the whole point we brought up.
March 31, 2015 at 1:12 pm
Ed Wagner (3/31/2015)
LightVader (3/31/2015)
But what if the first name has a space in the middle? The way the code is written it will return the wrong result in that case. Off-hand, I can't think of any method that would solve both problems.That was the whole point we brought up.
I understood that. I guess I wanted to be a little more explicit that Luis's sample code works for one way but not the other. Or more specifically that it can only work for one interpretation at a time. 😀
March 31, 2015 at 1:15 pm
LightVader (3/31/2015)
Ed Wagner (3/31/2015)
LightVader (3/31/2015)
But what if the first name has a space in the middle? The way the code is written it will return the wrong result in that case. Off-hand, I can't think of any method that would solve both problems.That was the whole point we brought up.
I understood that. I guess I wanted to be a little more explicit that Luis's sample code works for one way but not the other. Or more specifically that it can only work for one interpretation at a time. 😀
Okay. I don't know of a foolproof way to split out names either, where the delimiter is allowed within the individual parts of the string. That must be why people usually design tables with names in separate columns. 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply