Need help splitting a column

Question

Post reply

Need help splitting a column

gresford

Say Hey Kid

Points: 703
More actions
November 29, 2007 at 11:10 am

#179581

I think this is a very easy question but I am a relatively new to T-SQL and I can't find anything to get the job done:
I inherited a database that I have since migrated from Access 2000 and now working on re-building it in SQL Server 2005. This is necessary due to the fact that the original developer had little database design experience and there are many extremely basic design violations; most of which have made there way into the front-end of the application.
I have a name column that was designed to contain both first and last name:
strName
Doe, John
I am attempting to atomize this column by separating the first name from the last name:
LastName
Doe
FirstName
John
The comma in the strName column is pretty much consistent throughout the table. Is there a function in T-SQL that can search the string for the comma or does this operation need to be programmed long-hand? I think if I can find that comma, this becomes a trivial task for me.
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

You can use charindex, or patindex along with substring

declare @Temptable table (pk int identity, Name varchar(50))

insert into @Temptable (Name)

select 'Doe, John' union

select 'Doe, Jane' union

select 'Dirt, Joe' union

select 'Cartoon, Joe'

select substring(Name, charindex(',', Name) + 1, 50) as FirstName, substring(Name, 1, charindex(',', Name) -1) as LastName

from @Temptable

Results

JoeCartoon

JoeDirt

JaneDoe

JohnDoe

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 2

check BOL for charstring and substring functions.....

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Prakash Heda (11/29/2007)
check BOL for charstring and substring functions.....

Heh... "CharString"? Better have a look at BOL yourself 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gresford Say Hey Kid Points: 703 More actions · Answer 4

Thank you! A clever piece of coding that does the job.:D