November 9, 2009 at 8:37 am
Hi,
I am trying implement a solution to split a fullname column into firstname, lastname and middle lnitial. Thing is the data input isn't consistent in the fullname field. Names can have spaces or comma as seperators.
Sample Data:
FullName
Doe, Jon
Doe Jane
Smith Tom C.
Thomas, Al
last, first m.
Any help is greatly appreciated. Thanks.
Costa
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
November 9, 2009 at 2:40 pm
I'd recommend something like this:
DECLARE @temp TABLE (fullname VARCHAR(50))
INSERT INTO @temp (
fullname
)
SELECT
'Doe, Jon'
UNION ALL
SELECT
'Doe Jane'
UNION ALL
SELECT
'Smith Tom C.'
UNION ALL
SELECT
'Thomas, Al'
UNION ALL
SELECT
'last, first m.'
SELECT
/* The inner REPLACE removes spaces and the outer REPLACE removes doubled commas */
REPLACE(REPLACE (fullname, ' ', ','), ',,', ',')
FROM
@temp
This code gives you a comma delimited list for each name to split on. You should be able to get it from here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply