Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bendertez
Yak Posting Veteran
94 Posts |
 Posted - 2007-09-10 : 05:24:52
|
| HelloI know this is a relatively simple task , but how do you split one single firstname field in to two fields i.e first and middle for all records in sql server?I want the new first name field to obviuosly be the first part of the name. However, if there is not a middle name then I want the new first name field to have the name in and the new middle name field to be NULL.i.efirstnamefieldAnne MarieSarah JaneJennyin toNewFirstnamefield AnneSarahJennyNewMiddlenamefieldMarieJayneNULL (I don't want the word NULL, i just want an empty field)The following code nearly gets me there, but if there is no middle name it returns the first name in the new middle name field and not the new first name field.SUBSTRING(table.forenames, 0, CHARINDEX(' ',table.forenames)) AS 'First Name',SUBSTRING(table.forenames,CHARINDEX(' ', table.forenames),100) AS 'Middle Name',Any ideas?Thank you |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
Extracting middle name from first name field