September 28, 2009 at 3:24 pm
I have a data column called fullname I need to separate the first and last name.
FULLNAMe
Astohn, Tom
Jones,Artie
Would charindex be the best approach?
September 28, 2009 at 3:28 pm
It'll depend completely on your specific data.
For example, do you have any entries that aren't simply last-comma-first? Like "Smith, Jr, John" ("John Smith, Jr")? Do they all have commas?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 3:41 pm
Seconded ^^. If a comma is truly your delimiter then yes, it can be done by substringing the CHARINDEX position of the delimiter.
DECLARE @Names TABLE(
FullName VARCHAR(255)
)
INSERT INTO @Names(FullName) VALUES('Astohn, Tom')
INSERT INTO @Names(FullName) VALUES('Jones,Artie')
SELECT
n.FullName,
LTRIM(RTRIM(SUBSTRING(n.FullName, CHARINDEX(',', n.FullName)+1, LEN(n.FullName)))) AS FirstNameParsed,
SUBSTRING(n.FullName, 1, CHARINDEX(',', n.FullName)-1) AS LastNameParsed
FROM @Names n
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply