15,613,873 members
1.00/5 (1 vote)
See more:
My SQL query is returning a value like 'a,b,c'.
I want to replace last comma with 'and', finally it should looks like 'a,b and c' . Please help.
Posted
Updated 26-Jul-21 1:12am
Kuthuparakkal 24-Aug-12 2:08am
I am very bad at guessing your Backend.. Astrologically I feel it's Ruby on Rails

## Solution 1

see this
SQL
```declare @str
set @str='a'
select case when charindex(',',reverse(@str))>0
then
reverse(replace(STUFF(reverse(@str),charindex(',',reverse(@str)),0,'#'),'#,',' dna '))
else
@str
end```

Happy Coding!
:)

v2
vivektp 24-Aug-12 2:54am
But I wonder this will not work if my string is 'a'.Returns NULL
Aarti Meswania 24-Aug-12 3:09am
see updated solution
vivektp 24-Aug-12 5:23am
This will return always 'a'
Aarti Meswania 24-Aug-12 5:31am
use this function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
(
@str as varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
declare @result as varchar(max)
select @result= case when charindex(',',reverse(@str))>0
then
reverse(replace(STUFF(reverse(@str),charindex(',',reverse(@str)),0,'#'),'#,',' dna '))
else
@str
end
return @result
END
GO

------------------------
to execute & check function try some Queries given below

## Solution 3

SQL
```--Here is a simple solution, might be it will help you.

DECLARE @str VARCHAR(100)='a,b,c' -- pass any comma separated string here

SELECT REVERSE(STUFF(REVERSE(@str),CHARINDEX(',',REVERSE(@str)),1,' dna '))

--Output    a,b and c```

CHill60 26-Jul-21 7:52am
If you pass 'ABC' to this function it returns NULL, whereas I believe it should return 'ABC'.
Although I suspect you have received the downvote because the question is 9 years old and already has an adequate solution. Best stick to newer questions where the OP still needs help