how to extract all digit in nvarchar variable in ms sql-CodePudding

I wanna extract all digit in nvarchar variable and store them in a table or pre-declare variable. example:

declare @natioanlCode nvarchar(10) = '0083441141';

and the target result may be like this.

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;

CodePudding user response：

Another implementation of a recursive cte, but this time without substring since you don't seem to like that

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

See also this DBFiddle

CodePudding user response：

If the length of the string is unknown, you can use a recursive cte approach:

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