May 6, 2011 at 8:04 am
Okay guys, I'm thinking this should be easy for you this morning, but I'm thinking that maybe my coffee isn't working or something, because I can't get it to work. I have a name field that has lastname, firstname in the same field. I'm trying to break that out, so I run this query:
SELECT
LEFT(Name1, CHARINDEX(',', Name1)) as LastName,
STUFF(Name1, 1, CHARINDEX(',', Name1), '') as FirstName
FROM [ZORTEC].[LGC].[DBO].[UBMAST]
This works; however, the comma that is in the name1 field, ends up in the LastName field. So, I want to do CHARINDEX(',', Name1)-1, but for the life of me, I can't seem to get it to work. Figured I would post it out here to get a different set of eyes on it.
Thanks,
Jordon
May 6, 2011 at 8:35 am
The Charindex finds the location of the first comma, you just need to subtract 1 from it
Declare @t table (Name1 varchar(30))
insert into @t
values('Rondo, Rajon'),
('Garnett, Kevin'),
('Allen, Ray'),
('Pierce, Paul')
SELECT
LEFT(Name1, CHARINDEX(',', Name1) - 1) as LastName,
STUFF(Name1, 1, CHARINDEX(',', Name1), '') as FirstName
FROM @t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2011 at 8:36 am
It's actually very simple, but if you have any records that are missing a comma, you will get an index out of range error message. You can fix this by making sure that every string has at least one comma, by adding one at the end.
SELECT
LEFT(Name1, CHARINDEX(',', Name1 + ',') - 1) as LastName,
STUFF(Name1, 1, CHARINDEX(',', Name1 + ','), '') as FirstName
FROM [ZORTEC].[LGC].[DBO].[UBMAST]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 6, 2011 at 8:58 am
drew.allen (5/6/2011)
It's actually very simple, but if you have any records that are missing a comma, you will get an index out of range error message. You can fix this by making sure that every string has at least one comma, by adding one at the end.
SELECT
LEFT(Name1, CHARINDEX(',', Name1 + ',') - 1) as LastName,
STUFF(Name1, 1, CHARINDEX(',', Name1 + ','), '') as FirstName
FROM [ZORTEC].[LGC].[DBO].[UBMAST]
Drew
That's the problem. I was actually doing it right, but I was getting an error because some of the fields didn't have columns, since the name was a business name and didn't follow the standard lastname, firstname format. At least I know that I'm not a complete idiot!
Thanks for all your help!!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply