|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Splitting 1 column into 2Pretty basic but I cannot figure it out.
Column: FULLNAME (lname, fname) Needs to be FNAME LNAME Should split at the comman. I already have all the columns created; just need to SQL command to make it happen. Thanks!! *** Sent via Developersdex http://www.developersdex.com *** Assuming that all of your data takes the same format and that there are no
other embedded commas, give these a try. select left(@FullName, charindex(',', @FullName) - 1) as LastName , right(@FullName, charindex(',', @FullName) - 2) as FirstName1 , ltrim(right(@FullName, charindex(',', @FullName) - 1)) as FirstName2 -- if the number of spaces vary -- Show quote--Brian (Please reply to the newsgroups only.) "Joey Martin" <j***@infosmiths.net> wrote in message news:eX6TNd3nFHA.572@TK2MSFTNGP15.phx.gbl... > > > Pretty basic but I cannot figure it out. > > Column: > FULLNAME (lname, fname) > > Needs to be > > FNAME > LNAME > > Should split at the comman. I already have all the columns created; just > need to SQL command to make it happen. Thanks!! > > > > *** Sent via Developersdex http://www.developersdex.com *** Sorry, may not have been clear enough. I need to update FNAME1 and
LNAME1 from FULLNAME Sample of FULLNAME is Doe, John End result should be LNAME1=Doe FNAME1=John Tablename is customerdata *** Sent via Developersdex http://www.developersdex.com *** On Fri, 12 Aug 2005 12:51:58 -0700, Joey Martin wrote:
Show quote > Hi Joey,> >Pretty basic but I cannot figure it out. > >Column: >FULLNAME (lname, fname) > >Needs to be > >FNAME >LNAME > >Should split at the comman. I already have all the columns created; just >need to SQL command to make it happen. Thanks!! This should work: UPDATE MyTable SET LName = LEFT(FullName, CHARINDEX(', ', FullName) - 1), RName = RIGHT(FullName, CHARINDEX(' ,', REVERSE(FullName)) - 1) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics