SQL Server比较数据并一起替换

Question

我有两个字符串，如下:

 ***ABCABCABCABCABC***
 BBBTTTTTTTTTTTTTTTCCC

我想只从字符串

下面替换* position

所以结果可能是:

 BBBABCABCABCABCABCCCC
 BBBTTTTTTTTTTTTTTTCCC

有什么办法有优雅的解决方案，使用SQL Server功能?

我已经在下面做过了。

 DECLARE @Value1=PATINDEX('%[A,C,G,T]%', '***ABCABCABCABCABC***')
 Select SUBSTRING('BBBTTTTTTTTTTTTTTTBBB', 0, @Value1)
 ....

但这是必需的许多if else

我想知道是否有简单的方法来代替它

Answer 1

您可以使用:

DECLARE
   @String1 NVARCHAR(100) = '***ABCABCABCABCABC***',
   @String2 NVARCHAR(100) = 'BBBTTTTTTTTTTTTTTTCCC';
DECLARE @new_string NVARCHAR(100) = NULL;
;WITH nums AS
(
  SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY t1.number) AS N
  FROM master..spt_values t1 
  CROSS JOIN master..spt_values t2
), string_1 AS
(
  SELECT N, letter
  FROM nums
  CROSS APPLY (SELECT SUBSTRING(@String1, nums.N, 1) WHERE nums.N <= LEN(@String1)) 
    AS t(letter)
), string_2 AS
(
  SELECT N, letter
  FROM nums
  CROSS APPLY (SELECT SUBSTRING(@String2, nums.N, 1) WHERE nums.N <= LEN(@String2)) 
   AS t(letter)
), new_string AS
(
  SELECT Letter = CASE WHEN s1.Letter <> '*' THEN s1.Letter ELSE s2.Letter END,
         s1.N
  FROM string_1 s1
  LEFT JOIN string_2 s2
    ON s1.N = s2.N
)
SELECT @new_string = (SELECT '' + ISNULL(Letter, '')
                      FROM new_string
                      ORDER BY N ASC
                      FOR XML PATH(''));
                            
SELECT @new_string
UNION ALL
SELECT @String2;

1. 生成数字(可以使用任何方法)

2. 用字母分隔字符串1和2

3. 生成新的字符串，用相应的字母替换*

4. 字母连缀

备注:您可以将所有这些组合在一个查询中，但为了可读性，我将它们分开。

请随意将其包装为函数，使其优雅:

[dbo].[udf_merge_string](@string NVARCHAR(100), @string2 NVARCHAR(100)) ...