如何在ms sql中提取nvarchar变量中的所有数字

Question

我想提取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;

Answer 1

这里是@SalmanA的答案递归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中的示例

Answer 2

没有任何解释，我只能假设" creative ";意味着很多行代码，可能效率低下，而且难以理解。一个这样的解决方案是这样的:

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;