How can I insert each character of a string which is not comma delimited to separate row in a table?

Question

I have a string like

Declare @Str varchar (15) = '0123456789' 

and I have a table

CREATE TABLE TableNum(Nums Varchar(1));

How can I insert each number to a separate row?

My output should be

Nums
0
1
2
3
4
.
.

Answer 1

Using a numbers table is probably the best and simplest way to do it. If you don't alrady have a numbers table, you can create one:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Tally
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED (Number)

Then all you have to do is simply this:

INSERT INTO TableNum(Nums)
SELECT SUBSTRING(@Str, Number, 1)
FROM Tally
WHERE Number <= LEN(@Str)

For more information on why you want a numbers (or tally) table, read this article by Jeff Moden.

BTW, using varchar(1) makes no sense - use char(1) instead.

Answer 2

Try this : Using CTE you can achieve this, No need to use any joins with existing system tables

;WITH CTE AS(
     SELECT CAST('' AS NVARCHAR(1)) AS CH, 1 AS CNT
     UNION ALL
     SELECT CAST(SUBSTRING(@Str,CNT,1)AS NVARCHAR(1)) AS CH , CNT+1
     FROM CTE 
     WHERE CNT<=LEN(@Str)
)
INSERT INTO TableNum(Nums) 
SELECT CH FROM CTE WHERE CH <> ''

Answer 3

If you can't add a tally table (which the way to go as illustrated by Zohar Peled), you can use an ad-hoc tallty table

Declare @Str varchar (15) = '0123456789' 

Select Num=substring(@Str,N,1)
 From (Select Top (Len(@Str)) N=Row_Number() Over (Order By (Select null)) From master..spt_values ) N

Returns

Num
0
1
2
3
4
5
6
7
8
9

Another option is to create a TVF

Declare @Str varchar (15) = '0123456789' 

Select * from [dbo].[udf-Str-Parse-Char](@Str) 

The UDF

CREATE FUNCTION [dbo].[udf-Str-Parse-Char] (@String varchar(max))
Returns Table
As
Return (
    with   cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From cte1 a,cte1 b,cte1 c,cte1 d,cte1 e,cte1 f)

    Select RetSeq=N
          ,RetVal=Substring(@String,N,1) 
     From  cte2
) 
--Max 1 Million Observations
--Select * from [dbo].[udf-Str-Parse-Char]('this is a string') 

Answer 4

Try this

DECLARE @Str varchar(10) = '0123456789'
DECLARE @Count INT=1

WHILE @Count <= LEN(@Str)
BEGIN
    INSERT INTO TableNum
    SELECT substring(@Str, @count, 1)  
    SET @Count = @Count + 1
END

and then

SELECT * FROM TabNum

Answer 5

Declare @Str varchar (15) = '0123456789' 
        ,@pos INT, 
         @result VARCHAR(100); 

   SET @result = @Str; 
   SET @pos = 2 -- location where we want first space 
   WHILE @pos < LEN(@result)+1 
   BEGIN 
       SET @result = STUFF(@result, @pos, 0, SPACE(1)); 
       SET @pos = @pos+2; 

  END
  SELECT @result=REPLACE( @result,' ',',')
  --Print @result
  SELECT   Split.a.value('.', 'VARCHAR(100)') AS Num  
        FROM  
        (
            SELECT   
                 CAST ('<M>' + REPLACE(@result, ',', '</M><M>') + '</M>' AS XML) AS Num  

        ) AS A CROSS APPLY Num.nodes ('/M') AS Split(a)

---

OutPut

Num
0
1
2
3
4
5
6
7
8
9

Answer 6

Try this.

CREATE TABLE TableNum(Nums Varchar(1));
TRUNCATE TABLE TableNum;

Declare @Str varchar (15) = '0123456789';

DECLARE @nLIMIT AS INT = LEN(@Str);
DECLARE @nCounter AS INT = 1;

WHILE @nCounter <= @nLIMIT
BEGIN  

   INSERT INTO TableNum VALUES(SUBSTRING (@Str,@nCounter,1));

   SET @nCounter = @nCounter + 1;
   IF @nCounter > @nLIMIT
         BREAK  
   ELSE  
      CONTINUE  
 END;

SELECT * FROM TableNum;