我想提取 nvarchar 變數中的所有數字并將它們存盤在表中或預先宣告變數中。例子：
declare @natioanlCode nvarchar(10) = '0083441141';
并且目標結果可能是這樣的。
declare @n1 int = 0;
declare @n2 int = 0;
declare @n3 int = 8;
declare @n4 int = 3;
declare @n5 int = 4;
declare @n6 int = 4;
declare @n7 int = 1;
declare @n8 int = 1;
declare @n9 int = 4;
declare @n10 int = 1;
uj5u.com熱心網友回復：
遞回 cte 的另一種實作，但這次沒有子字串，因為您似乎不喜歡那樣
with test as (
select N'0083441141' as str
), rcte as (
select value = left(str, 1),
leftover = right(str, len(str) - 1)
from test
union all
select left(leftover, 1),
right(leftover, len(leftover) - 1)
from rcte
where len(leftover) > 0
)
select value
from rcte
另請參閱此DBFiddle
如果需要，您還可以將此結果插入到現有表中，請參閱此DBFiddle以獲取示例
uj5u.com熱心網友回復：
如果字串的長度未知，可以使用遞回 cte 方法：
with test as (
select N'12345678' as str union all
select N'123456789'
), rcte as (
select str, ordinal = 1, value = substring(str, 1, 1), leftover = substring(str, 2, len(str))
from test
union all
select str, ordinal 1, substring(leftover, 1, 1), substring(leftover, 2, len(leftover))
from rcte
where len(leftover) > 0
)
select *
-- into #asdf
from rcte
uj5u.com熱心網友回復：
沒有任何解釋，我只能假設“創造性”意味著多行代碼，可能效率低下且難以理解。一種這樣的解決方案是這樣的：
DECLARE @natioanlCode nvarchar(10) = '0083441141';
CREATE TABLE #temp (ID int IDENTITY,
I int)
DECLARE @C char(1);
WHILE LEN(@natioanlCode) > 0 BEGIN
SET @C = LEFT(@natioanlCode,1);
SET @natioanlCode = STUFF(@natioanlCode,1,1,'');
IF @C LIKE '[0-9]'
INSERT INTO #temp (I)
VALUES(@C)
ELSE
PRINT CONCAT(@C, N' isn''t a number');
END;
SELECT I
FROM #temp
ORDER BY ID;
DROP TABLE #temp;
當然，最簡單的方法是使用SUBSTRING，但這不是“創意”（但性能更高）：
DECLARE @natioanlCode nvarchar(10) = '0083441141';
SELECT SUBSTRING(@natioanlCode,V.I,1)
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))V(I)
WHERE TRY_CONVERT(int,SUBSTRING(@natioanlCode,V.I,1))
ORDER BY V.I;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/492358.html
上一篇：SQL：將長文本分成多行