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