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 |
|
kifeda
Posting Yak Master
136 Posts |
 Posted - 2007-06-04 : 14:51:59
|
| Hi All!I have a table, tblstudents that has the fields strfirstname and strlastname. The table was imported from MS Acess where they were storing the first name and the lastname in the same field. Sucks doesn't it? Anyway, I need to write a script to extract the first name from the lastname and insert it into the first name field which is blank. So far I figure I can use select into, but need to find a way to tell sql server to take the fart of the field after the comma (the first name) and to delete the comma becuase I won't need it anymore.Help!Select '%,' into tblclients as strfirstname from tblclientsI know this won't work, but I want to show you all I'm at least trying! |
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2007-06-04 : 14:54:41
|
| Check the help for CHARINDEX and SUBSTRING |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-04 : 16:08:15
|
| Provide some sample data and how you want to separate the strings and someone can help..As cvraghu mentioned you can pretty much acomplish it with th CHARINDEX and SUBSTRING functions if you can read up Books On Line.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
kifeda
Posting Yak Master
136 Posts |
Posted - 2007-06-04 : 16:52:20
|
| I currently have the data like this:strfirstname | strlstname jackson, fred walters, michaelI would like the data like this:strfirstname | strlastnamejackson fredmichael walters |
|
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-06-04 : 17:02:59
|
| Here's a link that may be able to help you:http://www.sql-server-helper.com/tips/split-name.aspxIn that link, it assumes that the first name and last name is separated by a space. You can change the logic to use a comma instead.SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-04 : 17:03:50
|
| [code]Declare @t table (strfirstname varchar(50), strlstname varchar(50))insert into @t values ('jackson, fred', null)insert into @t values ('walters, michael',null)select * from @t Update @t Set strlstname = substring (strfirstname, charindex(',', strfirstname) + 1, len(strfirstname) ), strfirstname = replace (strfirstname, substring (strfirstname, charindex(',', strfirstname), len(strfirstname) ), '')select * from @t [/code]Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-05 : 09:44:16
|
| Also, read about parsename function in sql server help fileMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
parse a field