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 |
francism
Starting Member
22 Posts |
Posted - 2008-11-12 : 10:53:02
|
I would like to split a column with data containing full names. The data is stored as First Last. I'd like to split the data into two: First and Last. The SQL below works well, except that, for people with more than two words to their name, e.g. "Shiu Fah Nancy Chiu", the first name is evaluated to one word, the rest are evaluated as last name, e.g. "Shiu" and "Fah Nancy Chiu". I would prefer the reverse, e.g. "Shiu Fah Nancy " and "Chiu".SELECT *,SUBSTRING(Cf_name, 1, CHARINDEX(' ', Cf_name) - 1) AS first_name,SUBSTRING(Cf_name, CHARINDEX(' ', Cf_name) + 1, LEN(Cf_name)) AS last_nameFROM Cf_BMODVPExtensionBaseorder by last_name, first_name Is this easily done?Thank you,Francis.Microsoft CRM 3 - SQL Server 2000 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-12 : 10:58:09
|
Yes. Failty easy. E 12°55'05.63"N 56°04'39.26" |
|
|
francism
Starting Member
22 Posts |
Posted - 2008-11-12 : 11:15:43
|
Thanks, that was helpful.Perhaps I should say "is this easily done, and if so, how?".Thanks you.Microsoft CRM 3 - SQL Server 2000 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 12:05:09
|
[code]SELECT LEFT(Cf_name,LEN(Cf_name)-CHARINDEX(' ', REVERSE(Cf_name))) AS first_name,RIGHT(Cf_name, CHARINDEX(' ', REVERSE(Cf_name))-1) AS last_nameFROM Cf_BMODVPExtensionBaseorder by last_name, first_name[/code] |
|
|
francism
Starting Member
22 Posts |
Posted - 2008-11-12 : 12:44:14
|
visakh16,I am a WebFOCUS (B.I. reporting language) expert and there isn't an easy way to do this, I should have tried reading up on documentation about SQL string functions. This works fantastically.Thank you!Francis.Microsoft CRM 3 - SQL Server 2000 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 12:50:03
|
quote: Originally posted by francism visakh16,I am a WebFOCUS (B.I. reporting language) expert and there isn't an easy way to do this, I should have tried reading up on documentation about SQL string functions. This works fantastically.Thank you!Francis.Microsoft CRM 3 - SQL Server 2000
Welcome |
|
|
|
|
|
|
|