SQL server varchar.toArray() or string.toArray() method

Question

I want to convert my string data to array in sql server. I tried it like 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 it is manual way, it is not the way of programmatic convertion as it needs me to key in every insert value for each line.

Could someone please suggest me more elegent 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 this INSERT using 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 that you do this in your application. SQL is optimised for Set based operations and does not handle recursive procedural tasks like like these.

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

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