Replace last occurrence of comma with 'and'

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

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 23-Aug-12 20:05pm

vivektp

Updated 26-Jul-21 1:12am

Add a Solution

Comments

Kuthuparakkal 24-Aug-12 2:08am

I am very bad at guessing your Backend.. Astrologically I feel it's Ruby on Rails

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Er Sagar Mahajan Pune · Answer 1 · 2021-07-26T01:12:00

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

Posted 26-Jul-21 1:12am

Er Sagar Mahajan Pune

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

Aarti Meswania · Answer 2 · 2012-08-23T20:32:00

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!
:)

Posted 23-Aug-12 20:32pm

Aarti Meswania

Updated 23-Aug-12 21:09pm

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('')