Click here to Skip to main content
15,036,891 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Comments
Kuthuparakkal 24-Aug-12 2:08am
   
I am very bad at guessing your Backend.. Astrologically I feel it's Ruby on Rails

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
Comments
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
CREATE FUNCTION fn_addand
(
@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
select dbo.fn_addand('a,b,c')
select dbo.fn_addand('a,b')
select dbo.fn_addand('a')
select dbo.fn_addand(Null)
select dbo.fn_addand('')
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
   
Comments
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900