removing partial string from column

how do i update a table which has like two strings in 1 column like

blog, joe ?

i want to strip the joe into a new field and the blog into another field

update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

i managed to strip the first name which is the string at the back but not the last name which is the string at the frontTry this to get the lastname:
lastname = left(column1, patindex('%,%', column1) - 1)|||The error message was :-

update Agency
set lastname = left(lastname, patindex('%,%', lastname) - 1)

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

Dario D'Alessandro, Dario
Stan Dickson, Stan
Ray John, Ray
Juan Sanchez, Juan

I trying to remove the Dario|||Gotta make sure there is at least one comma, or it errors out. Try this:

set lastname = left(lastname, charindex(',', lastname + ',') - 1)|||Thanks that did actually work.. not sure how that charindex worked. i was using to REPLACE statements to make it work. It was more efficient using your method.|||If you need a method of parsing name strings into component parts, I have a UDF that will do that too.

removing all but alphanumeric characters from strings

I'm cleaning dirty data. One of the tasks is to remove any characters that
aren't alpha or numeric from a string. Can I use the replace function with a
character expression? I tried replace(@.dirtystring, '[^0-9a-zA-Z]', '') and
it didn't replace anything... any ideas?
here's a good link
http://groups.google.com/groups?q=steve+kass+John!Wa43yn&hl=en&lr=&ie=UTF-8&group=microsoft.public.sqlserver.programming&sel m=3CE46DFB.E728741%40drew.edu&rnum=2
your question is probably better suited for the .programming group over the
..server group.
hth,
Eric
Ann Queue wrote:
> I'm cleaning dirty data. One of the tasks is to remove any
> characters that aren't alpha or numeric from a string. Can I use the
> replace function with a character expression? I tried
> replace(@.dirtystring, '[^0-9a-zA-Z]', '') and it didn't replace
> anything... any ideas?
|||http://www.nigelrivett.net/RemoveNon...haracters.html
"Ann Queue" wrote:

> I'm cleaning dirty data. One of the tasks is to remove any characters that
> aren't alpha or numeric from a string. Can I use the replace function with a
> character expression? I tried replace(@.dirtystring, '[^0-9a-zA-Z]', '') and
> it didn't replace anything... any ideas?