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 |
|
adz2013
Starting Member
6 Posts |
 Posted - 2007-01-09 : 11:57:40
|
| The name field in our database stores the name in the below format:Shields~Joseph A Simmons~Russell G Resig~Benjamin R Lindsey Jr.~Harold H Jordan~Adante D Kerr~Luke D Adkins~Guillermo B Conrad~Brian P I am trying to separate the last name and first name into 2 fields. I do not want the middle initial or Jr. in the field. I am using the statement below but i am running into an issue with names that have Jr. on them because there is an extra space in the name.SELECT SubString([Name],CHARINDEX('~',[Name])+1, (CHARINDEX(' ',[Name])-1) - (CHARINDEX('~', [Name])-1)) as FirstName,Left(Name,CHARINDEX('~',Name)-1) as LastNameFROM EmployeeWHERE StrtDate >'7/1/06' and StrtDate < '8/31/06' and Status = 'A' and BirthDate < GetDate() - 7665Any help would be appreciated. |
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
Issues with spaces in a field