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

Question

我想在sql服务器中将字符串数据转换为数组。我像下面这样尝试过。

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

但这是手动方式，不是编程转换的方式，因为它需要我键入每行的每个插入值。

有人可以建议我更优雅的方式吗？

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

使用master..spt_values中的number试试这个INSERT

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

输出：

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

Answer 3

我建议您在应用程序中执行此操作。SQL 针对基于 Set 的操作进行了优化，不处理此类递归过程任务。

如果要在SQL中执行此操作，可以找到字符串的LEN，然后递归查找下一个字符。像这样的东西

查询

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

输出

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

Answer 4

使用 cte 和 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

输出：

n   c
1   2
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9

或者您可以使用计数表来做到这一点：

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)

小提琴 http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/570