作者:王家刚163034 | 来源:互联网 | 2022-12-06 18:19
我有一个问题:在sql server中,如何将第二次出现的字符串替换happy
为字符串new
,否则保持原样(相同).
表:
CREATE TABLE [dbo].[stringrep](
[name] [varchar](100) NULL,
[id] [int] NULL
)
INSERT [dbo].[stringrep] ([name], [id]) VALUES (N'happy happy year', 1)
GO
INSERT [dbo].[stringrep] ([name], [id]) VALUES (N'very happy new year', 2)
GO
INSERT [dbo].[stringrep] ([name], [id]) VALUES (N'happy new year hello', 3)
GO
INSERT [dbo].[stringrep] ([name], [id]) VALUES (N'happy happy year', 4)
GO
INSERT [dbo].[stringrep] ([name], [id]) VALUES (N'heloo year happy', 5)
GO
INSERT [dbo].[stringrep] ([name], [id]) VALUES (N'happy happy happy year', 6)
GO
基于以上数据,我想要输出如下:
id | Name
1 | happy new year
2 | very happy new year
3 | happy new year hello
4 | happy new year
5 | heloo year happy
6 |happy new happy year
我尝试了以下查询:
SELECT replace ( name ,'happy happy year' ,'happy new year')afterreplacename,
replace ( name, substring ('happy happy year' ,6,6) ,' new')anotherway
,name ,[id]
FROM [test].[dbo].[stringrep]
上述查询未给出预期结果.
请告诉我如何在sql server中完成此任务.