玩玩SQL~字串置換變成要的字串資料

sql

純真的人 2021-09-06 11:20:10 ‧ 1110 瀏覽

分享至

這個我遇到有要轉換字串的工作@@~
雖然簡單~換大家試試看吧~XD

提供初始資料~

declare @Tmp table(
	SetStr nvarchar(50)
)
insert into @Tmp
values(N'012345678a')
,(N'A222a333B444')
,(N'A00gogo99家一這們我')

數字型態：(只會有個位數變化)
9-9=0
9-8=1
9-7=2

英文型態：(遇到大寫轉為小寫~遇到小寫轉為大寫)
A→a
a→A

其他型態：不做任何變化

最後將字串顛倒完成~

如圖所示：

我的方式如下~

declare @Tmp table(
	SetStr nvarchar(50)
)
insert into @Tmp
values(N'012345678a')
,(N'A222a333B444')
,(N'A00gogo99家一這們我')

select (
	select '' + StrChar
	from (
		select (
			case when isNumeric(StrTmp) = 1
			then Convert(nvarchar(50),9 - Convert(int,StrTmp))
			else 
				case 
				when UNICODE(StrTmp) between 97 and 122 then upper(StrTmp)
				when UNICODE(StrTmp) between 65 and 90 then lower(StrTmp)
				else StrTmp
				end
			end
		) StrChar
		,Sort
		from (
			select substring(SetStr,Sort,1) StrTmp
			,Sort
			from (
				select Row_Number()Over(order by [number]) as Sort
				from master..spt_values
			) as k
			where Sort <= len(SetStr)
		) k
	) k 
	order by Sort desc
	for xml path('')
) StrShow
from @Tmp

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 7 級 ‧ 2021-09-06 13:28:45

最佳解答

CREATE TABLE Tmp (
SetStr nvarchar(50));

INSERT INTO Tmp
values(N'012345678a'),
(N'A222a333B444'),
(N'A00gogo99家一這們我');

WITH CTE_X1 AS (
SELECT ROW_NUMBER() OVER (ORDER BY SetStr) AS NO,REVERSE(SetStr) AS SetStr
FROM Tmp
),
CTE_X2 AS (
SELECT NO,SetStr,
SUBSTRING(SetStr,1,1) AS StrNew,
1 AS NUM
FROM CTE_X1
UNION ALL
SELECT NO,SetStr,
SUBSTRING(SetStr,NUM + 1,1) AS StrNew,
NUM + 1 AS NUM
FROM CTE_X2
WHERE LEN(SetStr) - NUM>0
),
CTE_X3 AS (
SELECT *,
CASE WHEN ISNUMERIC(StrNew)=1 THEN CAST((9-StrNew) AS CHAR(1)) 
WHEN ASCII(StrNew)>=ASCII('A') AND ASCII(StrNew)<=ASCII('Z') THEN LOWER(StrNew)
WHEN ASCII(StrNew)>=ASCII('a') AND ASCII(StrNew)<=ASCII('z') THEN UPPER(StrNew) 
ELSE StrNew
END AS StrNew2
FROM CTE_X2 AS A
)

SELECT STRING_AGG (StrNew2,'') WITHIN GROUP (ORDER BY NO,NUM)
AS StrNew3
FROM CTE_X3
GROUP BY NO
ORDER BY NO

Demo