Replace data in a column for a character in SQL

Question

I have a table having data like as below:

  Description   Name
    ABC           AB
    ABCD          AB, BC, CD
    ABCDF         AB, BC

Now i needed output as below:

  Description   Name
    ABC           AB
    ABCD          AB, BC and CD
    ABCDF         AB and BC

How can i get desired output in SQL? please help me out.

Answer 1

Please try:

select 
    Description, 
    ISNULL(
       REVERSE(STUFF(REVERSE(Name), CHARINDEX(',', REVERSE(Name), 0),1,'dna ')), 
    Name) Name
From YourTable

SQL Fiddle Demo

Answer 2

Do it as

declare @str nvarchar(200)
set @str = 'Ali, ahmed, riaz, zoya'
select SUBSTRING(@str, 0, (len(@str) - charindex(',', reverse(@str)))) +
Replace(SUBSTRING(@str, (len(@str) - charindex(',', reverse(@str))), len(@str)), 
', ', ' and ')