Delete Characters After a Space in a String

I have had a quick search but can't seem to get a working method of this, and I can't imagine it is the most difficult thing to do.
I have a list of about 1000 names from a client, all in the 'surname, forename' format.
I am loading them into a database but want them to be in the 'Forename Surname' format.
I have managed to get some working code to get the forename only but now need something that will get the surname only, and a way to do that is get delete all characters after and including the comma.

Cheers in advance
LVL 4
Answering4uAntAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
bokistConnect With a Mentor Commented:
Try this way :

update my_table set name_field = substring(name_field, charindex(',', name_field) +1, 50) + ' ' +
                                                       substring(name_field, 1, charindex(',', name_field) -1)  
0
 
Answering4uAntAuthor Commented:
sorry topic title slightly wrong, I want to delete all characters after a comma but imagine the method will be pretty much the same

Cheers
0
 
Answering4uAntAuthor Commented:
Thanks, that did the job just fine, it did leave an extra space at the start which I have managed to remove easily enough

cheers
0
 
RiteshShahCommented:
declare @name varchar(50)
set @name='Ritesh,Shah'
select replace(@name,substring(@name,charindex(',',@name),len(@name)),'')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.