如何在sql server中用new替换第二个位置字符串 [英] how to replace second position string with new in sql server

问题描述

我有一个问题:在 sql server 中，如何将字符串 happy 的第二次出现替换为字符串 new，否则保持原样(相同).

I have one question: in sql server, how to replace the 2nd occurrence of the string happy to the string new, otherwise keep as it is(same).

表格:

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

基于以上数据，我想要如下输出:

based on above data I want output like below:

  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]

上面的查询没有给出预期的结果.

The above query does not give the expected result.

请告诉我如何在 sql server 中完成此任务.

Please tell me how to achive this task in sql server .

推荐答案

我想出了以下查询.替换逻辑仅针对 happy 至少出现两次的数据.如果是，那么我们找到happy第二次出现的索引，然后在new中找到STUFF作为替换.

I managed to come up with the following query. The replacement logic only targets data having happy occurring at least two times. If so, then we find the index of the second occurrence of happy, and then STUFF in new as a replacement.

SELECT
    data,
    CASE WHEN LEN(REPLACE(data, 'happy', '')) < LEN(data) - 6
         THEN STUFF(data,
                    CHARINDEX('happy', data, CHARINDEX('happy', data) + 1),
                     5,
                     'new')
         ELSE data END AS new_data
FROM yourTable;

演示

请注意，此解决方案对于字符串中任意位置出现两次(或多次)happy 是稳健的.请参阅此边缘情况的示例数据的最后一行.

Note that this solution is robust to the two (or more) occurrences of happy being anywhere in the string. See the last row of the sample data for this edge case.

我们更愿意在这里使用正则表达式，也许您的问题可以用一行来回答.但是，SQL Server 没有很好的原生正则表达式支持，迫使我们改用基本字符串函数.

We would rather like to use regex here, and maybe you question could be answered with a one-liner. But, SQL Server does not have good native regex support, forcing us to use the base string functions instead.

这篇关于如何在sql server中用new替换第二个位置字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！