我们如何在单词和最后一个单词之间添加"逗号"和"

Question

我试过了，但没有得到预期的结果：

SELECT reverse(STUFF(reverse(@test), CHARINDEX(',', reverse(@test)), 1, ' dna '))

输出应该像：

Pavan, Phani, Bhanu, Yaswanth, Prasanth and Brahmi

Answer 1

我认为这个查询运行良好。

declare @Input varchar(256) = 'Pavan Phani Bhanu Yaswanth Prasanth Brahmi'
select replace(replace(reverse(stuff(REVERSE(@Input), charindex(' ', REVERSE(@Input)), 1, ' dna ')), ' ', ', '), ', and,', ' and')

Answer 2

CREATE TABLE TEST (
Id int,
Value varchar(100)    
)
INSERT INTO TEST 
VALUES(1,'RASEL'),
(1,'NEHAN'),
(1,'NAFIZ'),
(1,'NABIL')
SELECT distinct T1.ID 
,Value = REVERSE(STUFF(REVERSE(STUFF((SELECT ', '+ t2.Value
FROM TEST t2
where t1.Id = t2.Id
FOR XML PATH (''))
, 1, 1, '') ), CHARINDEX(',', REVERSE(STUFF((SELECT ', '+ t2.Value
FROM TEST t2
where t1.Id = t2.Id
FOR XML PATH (''))
, 1, 1, ' '))), 1, 'dna '))
FROM TEST AS T1 

输出

ID  Value
1   RASEL, NEHAN, NAFIZ and NABIL

Answer 3

感谢各位提供信息终于解决了我的问题，得到了这个答案。

注意：这是工作n感谢您的帮助

DECLARE @Input VARCHAR(256) = 'Pavan Phani Bhanu Yaswanth Prasanth Brahmi'
select substring(@Input,1,5)+replicate(',',1)+SUBSTRING(@input,6,7)+replicate(',',1)+
substring(@Input,12,7)+replicate(',',1)+substring(@input,18,10)+replicate(',',1)+substring(@input,27,10)+replicate('and',1) +substring(@input,37,5)