iT邦幫忙

0

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

sql
  • 分享至 

  • xImage

這個我遇到有要轉換字串的工作@@~
雖然簡單~換大家試試看吧~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

其他型態:不做任何變化

最後將字串顛倒完成~

如圖所示:
https://ithelp.ithome.com.tw/upload/images/20210906/200613690zDCvAsAVJ.png


我的方式如下~

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 }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

6
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

喔喔~厲害呢~WITH真是萬用

我要發表回答

立即登入回答