在SQL中如何融合两个字符串

[日月路明]

如：这样的两个字符串
123X56X89
12XX56789
相同位置的X用另外一个字符串中非X字符替代，也就是，上面两个融合后就是
123X56789
第四位两个字符串都是X，只能保留

一个大的前提：如果相同位置都不是X，一定是相同的

[顺势而为1]

DECLARE @str1 varchar(9)
DECLARE @str2 varchar(9)
SET @str1='123X56X89'
SET @str2='12XX56789'
DECLARE @TagStr varchar(9)
SET @TagStr=''
DECLARE @i int
SET @i=1
DECLARE @iLen int
SET @iLen=Len(@str1)

While @i<=@iLen
     Begin
	     IF @i<>4
	          SET @TagStr=@TagStr+Case when substring(@str1,@i,1)<>'X' then substring(@str1,@i,1) else  substring(@str2,@i,1) end
		 Else
		 	  SET @TagStr=@TagStr+'X'

		 set @i+=1

	 End

Select @TagStr

---------
123X56789

[RINK_1]

IF OBJECT_ID(N'TEMPDB..#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T(CODE VARCHAR(20))

INSERT INTO #T
SELECT '123X56X89' UNION ALL
SELECT '12XX56789' UNION ALL
SELECT '12X056789'

WITH CTE_1
AS
(SELECT A.CODE,
SUBSTRING(CODE,number,1) AS SINGLE_CHAR,
NUMBER,
SEQ
FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY GETDATE()) AS SEQ  FROM #T) AS A
JOIN master.dbo.spt_values B ON number<=LEN(CODE)
WHERE TYPE='P' 
AND number>0),

CTE_2
AS
(SELECT A.CODE,CASE WHEN A.SINGLE_CHAR='X' THEN B.SINGLE_CHAR ELSE A.SINGLE_CHAR END AS SINGLE_CHAR,
 CAST(A.CODE AS VARCHAR)+'-'+CAST(B.CODE AS VARCHAR) AS GROUP_CODE FROM CTE_1 A
JOIN CTE_1 B ON A.SEQ<B.SEQ AND A.number=B.number)

SELECT DISTINCT CODE,
GROUP_CODE,
(SELECT CAST(SINGLE_CHAR AS VARCHAR) FROM CTE_2 WHERE A.GROUP_CODE=GROUP_CODE FOR XML PATH('')) AS NEW_CODE 
FROM CTE_2 A

[日月路明]

大开眼界，大家都特别喜欢使用with，

引用 3 楼 appetizing_fish1 的回复:

DECLARE @str1 varchar(9)
DECLARE @str2 varchar(9)
SET @str1='123X56X89'
SET @str2='12XX56789'
DECLARE @TagStr varchar(9)
SET @TagStr=''
DECLARE @i int
SET @i=1
DECLARE @iLen int
SET @iLen=Len(@str1)

While @i<=@iLen
     Begin
	     IF @i<>4
	          SET @TagStr=@TagStr+Case when substring(@str1,@i,1)<>'X' then substring(@str1,@i,1) else  substring(@str2,@i,1) end
		 Else
		 	  SET @TagStr=@TagStr+'X'

		 set @i+=1

	 End

Select @TagStr

---------
123X56789

我自己的做法和你一样，只不过 IF @i<>4 这个判断是没必要的

[顺势而为1]

引用 5 楼 yisuylm 的回复:

大开眼界，大家都特别喜欢使用with， [quote=引用 3 楼 appetizing_fish1 的回复:]

DECLARE @str1 varchar(9)
DECLARE @str2 varchar(9)
SET @str1='123X56X89'
SET @str2='12XX56789'
DECLARE @TagStr varchar(9)
SET @TagStr=''
DECLARE @i int
SET @i=1
DECLARE @iLen int
SET @iLen=Len(@str1)

While @i<=@iLen
     Begin
	     IF @i<>4
	          SET @TagStr=@TagStr+Case when substring(@str1,@i,1)<>'X' then substring(@str1,@i,1) else  substring(@str2,@i,1) end
		 Else
		 	  SET @TagStr=@TagStr+'X'

		 set @i+=1

	 End

Select @TagStr

---------
123X56789

我自己的做法和你一样，只不过 IF @i<>4 这个判断是没必要的[/quote] 如果两个字符串的第4位本来都是X, 那就不用加这个条件.

[日月路明]

看了楼上几位的写法，发现特别喜欢使用with...这种方式，可惜我的是sql 2000,我理解了一下，用sql2000写的话就是这个意思 DECLARE @s1 VARCHAR(20),@s2 VARCHAR(20),@s3 varchar(20) SET @s1='123X56X89' SET @s2='12XX56789' declare @i int create table #t1(i int) set @i=1 while @i<=Len(@s1) BEGIN insert into #t1 values(@i) set @i=@i+1 END set @s3='' select @s3=@s3+case when s1='X' then s2 else s1 end from( select s1=substring(@s1,i,1),s2=subString(@s2,i,1) from #t1 )as t print @s3 这种写法 确实颠覆了我的固有思维，但我个人认为，相比较#3楼的写法，没有明显的优势，不知道是否还有其他方法，再等一天就结贴

[顺势而为1]

只循环X看看效率会不会高些

DECLARE @str1 varchar(9)
SET @str1='123X56X89'
DECLARE @str2 varchar(9)
SET @str2='12XX56789'

--将第4位X用Y代替
DECLARE @str1Aid varchar(9)
SET @str1Aid=substring(@str1,1,3)+'Y'+substring(@str1,5,len(@str1)-4)
--计算X个数
DECLARE @iXqty int
SET @iXqty=len(@str1Aid)-len(Replace(@str1Aid,'X',''))
DECLARE @i int
 

SET @i=1
While @i<=@iXqty
     Begin
           SET @str1Aid=Replace(@str1Aid,'X',Substring(@str2,Charindex('X',@str1Aid),1))
           SET @i=@i+1
     End

SET @str1=substring(@str1Aid,1,3)+'X'+substring(@str1Aid,5,len(@str1Aid)-4)
Select @str1


123X56789

[日月路明]

现在的问题，我主要是觉得自己写的代码太罗嗦，希望得到精简高效的融合算法

[RINK_1]

不用WITH的话，就试试临时表。

declare @code_1 VARCHAR(20)
declare @code_2 VARCHAR(20)

set @code_1='123X56X89'
set @code_2='12XX56789'


SELECT A.CODE,
SEQ,
SUBSTRING(CODE,number,1) AS SINGLE_CHAR, 
NUMBER
INTO #A
FROM (select @code_1 as CODE,1 as SEQ union all
      select @code_2,2) AS A
JOIN master.dbo.spt_values B ON number<=LEN(CODE)
WHERE TYPE='P' 
AND number>0

 
declare @new_code varchar(20)

select @new_code=isnull(@new_code,'')+SINGLE_CHAR_NEW
from
(SELECT A.number,
CASE WHEN A.SINGLE_CHAR='X' THEN B.SINGLE_CHAR ELSE A.SINGLE_CHAR END AS SINGLE_CHAR_NEW
FROM #A AS A
JOIN #A B ON A.SEQ=1 AND B.SEQ=2  AND A.number=B.number) as A
order by A.number

print @new_code
 

DROP TABLE #A

[吉普赛的歌]

DECLARE @s1 VARCHAR(20),@s2 VARCHAR(20)
SET @s1='123X56X89'
SET @s2='12XX56789'

;WITH tt1 AS (
	SELECT sv.number AS id,SUBSTRING(t1.c,sv.number,1) AS c 
	FROM (SELECT @s1 AS c)AS t1 CROSS APPLY [master].dbo.spt_values AS sv 
	WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND LEN(t1.c)
), tt2 AS (
	SELECT sv.number AS id,SUBSTRING(t2.c,sv.number,1) AS c 
	FROM (SELECT @s2 AS c)AS t2 CROSS APPLY [master].dbo.spt_values AS sv 
	WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND LEN(t2.c)
)
SELECT (
	SELECT  
	CASE WHEN a.c=b.c THEN a.c
		 WHEN a.c='X' THEN b.c
		 ELSE a.c END 
	FROM tt1 AS a INNER JOIN tt2 AS b ON a.id=b.id
	FOR XML PATH('')
) AS result
/*
result
123X56789
*/