Varchar.toArray () SQL Server or string.toArray () Method

Question

I want to convert my string data to an array in sql server. I tried this as shown below.

SELECT '223456789' AS SerialOriginalCode -- SerialOriginalCode 223456789 DECLARE @tbl_SerialOriginalVerion TABLE(ID INT, SerialOriginalCode INT); INSERT INTO @tbl_SerialOriginalVerion VALUES (1, 2), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9); SELECT * FROM @tbl_SerialOriginalVerion 

But this is a manual way, it is not a program conversion way, because I need me to insert every insert value for each row.

Can someone please suggest me a more elegant way?

Answer 1

 DECLARE @InputText AS VARCHAR(MAX) = '223456789' DECLARE @Pos Int = 1 DECLARE @End Int DECLARE @TextLength Int = DATALENGTH(@InputText) DECLARE @Array TABLE ( TokenID Int PRIMARY KEY IDENTITY(1,1), Match Varchar(MAX) ) -- Exit function if no text is passed in. IF @TextLength <> 0 BEGIN WHILE @Pos <= @TextLength BEGIN INSERT @Array (Match) VALUES (SUBSTRING(@InputText,@Pos,1)) SET @Pos = @Pos + 1 END END SELECT * FROM @Array 

Answer 2

Try INSERT with number from master..spt_values

 SELECT '223456789' AS SerialOriginalCode -- SerialOriginalCode 223456789 DECLARE @tbl_SerialOriginalVerion TABLE(ID INT, SerialOriginalCode INT); INSERT INTO @tbl_SerialOriginalVerion SELECT number + 1, SUBSTRING(t.SerialOriginalCode, sv.number + 1, 1) FROM (SELECT '223456789' AS SerialOriginalCode) t INNER JOIN master..spt_values sv ON sv.number < LEN(t.SerialOriginalCode) WHERE sv.[type] = 'P' SELECT * FROM @tbl_SerialOriginalVerion 

Output:

 ID SerialOriginalCode 1 2 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 

Answer 3

I would recommend you do this in my application. SQL is optimized for set-based operations and does not handle recursive procedural tasks like these.

If you want to do this in SQL, you can find the LEN your string, and then recursively find the next character. Something like that

Query

 DECLARE @SerialOriginalCode VARCHAR(20) = '223456789' DECLARE @tbl_SerialOriginalVerion TABLE(ID INT, SerialOriginalCode INT); DECLARE @len INT = LEN(@SerialOriginalCode) ;WITH CTE as ( SELECT 1 as ID,CONVERT(INT,SUBSTRING(@SerialOriginalCode,1,1)) as CharInt UNION ALL SELECT ID + 1,CONVERT(INT,SUBSTRING(@SerialOriginalCode,ID + 1,1)) FROM CTE WHERE LEN(@SerialOriginalCode) >= ID + 1 ) INSERT INTO @tbl_SerialOriginalVerion(ID,SerialOriginalCode) SELECT * FROM CTE; SELECT * FROM @tbl_SerialOriginalVerion 

Output

 ID SerialOriginalCode 1 2 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 

Answer 4

Using cte and Apply:

 Declare @s nvarchar(9) = '223456789' ;with cte as( select n from (values(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(n)) select * from cte cross apply(select substring(@s, cte.n, 1) as c) ca 

Output:

 nc 1 2 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 

Or you can do this with table tables:

 declare @s varchar(100) = '223456789' ;with t as(select row_number() over(order by (select null)) rn from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t1(n) cross join (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t2(n)) select substring(@s, rn, 1) from t where rn <= len(@s) 

Fiddle http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/570