last/first name string

I need to seperate a last name, first name string. I am migrating data from FoxPro then merging it with data that has been migrated from Paradox. In Paradox the name is kept in: "last, first" format. I need to reformat the name to be: first(space)last. Suggestions...Based on the assumption that the first comma in the string marks the end of the last name, you could use
SELECT @.comma = CharIndex(',', @.paradoxName)
SELECT @.last = LTrim(RTrim(Left(@.paradoxName, @.comma - 1)))
SELECT @.first = LTrim(RTrim(SubString(@.paradoxName, 1 + @.comma, 8000)))-PatP|||select reformatted_name = replace(paradox_name, ', ', ' ') from paradox_table where charindex(', ', paradox_name) > 0|||Originally posted by rdjabarov
select reformatted_name = replace(paradox_name, ', ', ' ') from paradox_table where charindex(', ', paradox_name) > 0

This takes care of the comma but doesnt solve the position of the names. Thanks though...|||select reverse(substring(reverse('last, first'), 1, charindex(', ', 'last, first'))) + ' ' +
substring('last, first', 1, charindex(', ', 'last, first')-1)|||Just advice - keep last and first names in different fields.|||Originally posted by snail
Just advice - keep last and first names in different fields. I'll drink to that!|||I was kidding - April 1 ;)|||Originally posted by Pat Phelan
Based on the assumption that the first comma in the string marks the end of the last name, you could use
SELECT @.comma = CharIndex(',', @.paradoxName)
SELECT @.last = LTrim(RTrim(Left(@.paradoxName, @.comma - 1)))
SELECT @.first = LTrim(RTrim(SubString(@.paradoxName, 1 + @.comma, 8000)))-PatP

Pat,
this worked like a charm. Thanks a million.|||Pat does strings...|||Originally posted by rdjabarov
select reverse(substring(reverse('last, first'), 1, charindex(', ', 'last, first'))) + ' ' +
substring('last, first', 1, charindex(', ', 'last, first')-1) You really are evil, but I think you'd already figured that part out!

-PatPsql