通过 select 语句拆分字符串 [英] Split string via select statement

问题描述

相当简单的问题，但似乎不可能得到答案.

Fairly simple question, but seems impossibe to get an answer.

如果我有这个:

declare @Agent nvarchar(4000) = '2131235,334225';

是否有我可以编写的选择语句来拆分昏迷所在的字符串，而不必编写函数?

Is there a select statement I can write to split the string where the coma is, WITHOUT having to write a function?

类似于:

SELECT SOME_SPLIT_LOGIC(@Agent)

我希望它返回:

Column
--------
2131235
334225

请注意我使用的是 MS-SQL 2012

Please note I am use MS-SQL 2012

推荐答案

怎么样:

DECLARE @Agent VARCHAR(4000) = '2131235,334225'
DECLARE @Delimiter VARCHAR(1) = ','

;WITH CTE
AS
(
    SELECT 1 AS ID 
    UNION ALL
    SELECT ID + 1 FROM CTE
    WHERE ID < 4000
)   

SELECT
    SUBSTRING(@Agent, t.ID, CHARINDEX(@Delimiter, @Agent + @Delimiter, t.ID) - t.ID) AS Agent
FROM CTE t
WHERE t.ID <= DATALENGTH(@Agent)+1
    AND SUBSTRING(@Delimiter + @Agent, t.ID, 1) = @Delimiter
OPTION (MAXRECURSION 4000)

更多信息可以在这里找到:http://www.sqlservercentral.com/articles/Tally+Table/72993/

More information can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

这篇关于通过 select 语句拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！