逗号分隔值的拆分 [英] Splitting of comma separated values

问题描述

我有一些带有逗号分隔值的列.例如

I have some columns with comma-separated values. For example

A

as,ad,af,ag

我的经理希望输出如下所示:

My manager wants the output looks like this:

as                          

ad          

af          

ag  

<小时>

所有这些都应该放在一行中.我想我们需要使用换行符.

---

All these should come under one row. I guess we need to use something line break.

我们可以用 char(13)+char(10) 替换吗?这个也有效...

Hi can we use replace with char(13)+char(10) something. This one also works...

谢谢，夏什拉

推荐答案

您可以创建一个用户定义的 UDF，如下所示.然后，只需从另一个查询中传入逗号分隔的列表，它就会返回一个表，每个值都在单独的行中.

You can create a user defined UDF like the one shown below. Then, just pass in the comma separated list from another query and it will return a table with each value in a separate row.

CREATE FUNCTION [dbo].[fnSplitStringAsTable] 
(
    @inputString varchar(MAX),
    @delimiter char(1) = ','
)
RETURNS 
@Result TABLE 
(
    Value varchar(MAX)
)
AS
BEGIN
    DECLARE @chIndex int
    DECLARE @item varchar(100)

    -- While there are more delimiters...
    WHILE CHARINDEX(@delimiter, @inputString, 0) <> 0
        BEGIN
            -- Get the index of the first delimiter.
            SET @chIndex = CHARINDEX(@delimiter, @inputString, 0)

            -- Get all of the characters prior to the delimiter and insert the string into the table.
            SELECT @item = SUBSTRING(@inputString, 1, @chIndex - 1)

            IF LEN(@item) > 0
                BEGIN
                    INSERT INTO @Result(Value)
                    VALUES (@item)
                END

            -- Get the remainder of the string.
            SELECT @inputString = SUBSTRING(@inputString, @chIndex + 1, LEN(@inputString))
        END

    -- If there are still characters remaining in the string, insert them into the table.
    IF LEN(@inputString) > 0
        BEGIN
            INSERT INTO @Result(Value)
            VALUES (@inputString)
        END

    RETURN 
END

这篇关于逗号分隔值的拆分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！