如何将逗号分隔的值拆分为列

ybzsozfc  于 2021-07-24  发布在  Java
关注(0)|答案(30)|浏览(910)

我有一张这样的table

Value   String
-------------------
1       Cleo, Smith

我想把逗号分隔的字符串分成两列

Value  Name Surname
-------------------
1      Cleo   Smith

我只需要两个固定的额外柱子

sql-servercsvsql-server-2008

30条答案

按热度按时间
r1zhe5dt

r1zhe5dt1#

我在上面重新写了一个答案，让它变得更好：

CREATE FUNCTION [dbo].[CSVParser]
(
  @s        VARCHAR(255),
  @idx      NUMERIC
)
RETURNS VARCHAR(12)
BEGIN
    DECLARE @comma int
    SET @comma = CHARINDEX(',', @s)
    WHILE 1=1
    BEGIN
        IF @comma=0
            IF @idx=1
                RETURN @s
            ELSE
                RETURN ''

        IF @idx=1
        BEGIN
            DECLARE @word VARCHAR(12)
            SET @word=LEFT(@s, @comma - 1)
            RETURN @word
        END

        SET @s = RIGHT(@s,LEN(@s)-@comma)
        SET @comma = CHARINDEX(',', @s)
        SET @idx = @idx - 1
    END
    RETURN 'not used'
END

用法示例：

SELECT dbo.CSVParser(COLUMN, 1),
       dbo.CSVParser(COLUMN, 2),
       dbo.CSVParser(COLUMN, 3)
FROM   TABLE
赞(0）分享  回复(0）举报  2021-07-24
ne5o7dgx

ne5o7dgx2#

Select distinct PROJ_UID,PROJ_NAME,RES_UID from E2E_ProjectWiseTimesheetActuals
where   CHARINDEX(','+cast(PROJ_UID as varchar(8000))+',', @params) > 0 and  CHARINDEX(','+cast(RES_UID as varchar(8000))+',', @res) > 0
赞(0）分享  回复(0）举报  2021-07-24
uqzxnwby

uqzxnwby3#

select distinct modelFileId,F4.*
from contract
cross apply (select XmlList=convert(xml, '<x>'+replace(modelFileId,';','</x><x>')+'</x>').query('.')) F2
cross apply (select mfid1=XmlNode.value('/x[1]','varchar(512)')
,mfid2=XmlNode.value('/x[2]','varchar(512)')
,mfid3=XmlNode.value('/x[3]','varchar(512)')
,mfid4=XmlNode.value('/x[4]','varchar(512)') from XmlList.nodes('x') F3(XmlNode)) F4
where modelFileId like '%;%'
order by modelFileId
赞(0）分享  回复(0）举报  2021-07-24
suzh9iv8

suzh9iv84#

我发现如上所述使用parsename会导致任何带有句点的名称为空。
因此，如果名称中有一个首字母或标题后跟一个点，则返回null。
我发现这对我有用：

SELECT 
REPLACE(SUBSTRING(FullName, 1,CHARINDEX(',', FullName)), ',','') as Name,
REPLACE(SUBSTRING(FullName, CHARINDEX(',', FullName), LEN(FullName)), ',', '') as Surname
FROM Table1
赞(0）分享  回复(0）举报  2021-07-24
vd2z7a6w

vd2z7a6w5#

ALTER function get_occurance_index(@delimiter varchar(1),@occurence int,@String varchar(100))
returns int
AS Begin
--Declare @delimiter varchar(1)=',',@occurence int=2,@String varchar(100)='a,b,c'
Declare @result int
 ;with T as (
    select 1 Rno,0 as row, charindex(@delimiter, @String) pos,@String st
    union all
    select Rno+1,pos + 1, charindex(@delimiter, @String, pos + 1), @String
    from T
    where pos > 0
)
select  @result=pos 
from T 
where pos > 0   and rno = @occurence 
return isnull(@result,0)
ENd

declare @data as table (data varchar(100))
insert into @data values('1,2,3') 
insert into @data values('aaa,bbbbb,cccc') 
select top  3 Substring (data,0,dbo.get_occurance_index( ',',1,data)) ,--First Record always starts with 0
Substring (data,dbo.get_occurance_index( ',',1,data)+1,dbo.get_occurance_index( ',',2,data)-dbo.get_occurance_index( ',',1,data)-1) ,
Substring (data,dbo.get_occurance_index( ',',2,data)+1,len(data)) , -- Last record cant be more than len of actual data
data 
From @data
赞(0）分享  回复(0）举报  2021-07-24
mrphzbgm

mrphzbgm6#

您可能会发现在sql用户定义函数中解析分隔字符串的解决方案很有帮助（来自代码项目）。
这是本页的代码部分：

CREATE FUNCTION [fn_ParseText2Table]
  (@p_SourceText VARCHAR(MAX)
  ,@p_Delimeter VARCHAR(100)=',' --default to comma delimited.
  )
 RETURNS @retTable
  TABLE([Position] INT IDENTITY(1,1)
   ,[Int_Value] INT
   ,[Num_Value] NUMERIC(18,3)
   ,[Txt_Value] VARCHAR(MAX)
   ,[Date_value] DATETIME
   )
AS
/*

********************************************************************************

Purpose: Parse values from a delimited string
  & return the result as an indexed table
Copyright 1996, 1997, 2000, 2003 Clayton Groom (<A href="mailto:Clayton_Groom@hotmail.com">Clayton_Groom@hotmail.com</A>)
Posted to the public domain Aug, 2004
2003-06-17 Rewritten as SQL 2000 function.
 Reworked to allow for delimiters > 1 character in length
 and to convert Text values to numbers
2016-04-05 Added logic for date values based on "new" ISDATE() function, Updated to use XML approach, which is more efficient.

********************************************************************************
* /

BEGIN
 DECLARE @w_xml xml;
 SET @w_xml = N'<root><i>' + replace(@p_SourceText, @p_Delimeter,'</i><i>') + '</i></root>';

 INSERT INTO @retTable
     ([Int_Value]
    , [Num_Value]
    , [Txt_Value]
    , [Date_value]
     )
     SELECT CASE
       WHEN ISNUMERIC([i].value('.', 'VARCHAR(MAX)')) = 1
       THEN CAST(CAST([i].value('.', 'VARCHAR(MAX)') AS NUMERIC) AS INT)
      END AS [Int_Value]
    , CASE
       WHEN ISNUMERIC([i].value('.', 'VARCHAR(MAX)')) = 1
       THEN CAST([i].value('.', 'VARCHAR(MAX)') AS NUMERIC(18, 3))
      END AS [Num_Value]
    , [i].value('.', 'VARCHAR(MAX)') AS [txt_Value]
    , CASE
       WHEN ISDATE([i].value('.', 'VARCHAR(MAX)')) = 1
       THEN CAST([i].value('.', 'VARCHAR(MAX)') AS DATETIME)
      END AS [Num_Value]
     FROM @w_xml.nodes('//root/i') AS [Items]([i]);
 RETURN;
END;
GO
赞(0）分享  回复(0）举报  2021-07-24
798qvoo8

798qvoo87#

DECLARE @INPUT VARCHAR (MAX)='N,A,R,E,N,D,R,A'
DECLARE @ELIMINATE_CHAR CHAR (1)=','
DECLARE @L_START INT=1
DECLARE @L_END INT=(SELECT LEN (@INPUT))
DECLARE @OUTPUT CHAR (1)

WHILE @L_START <=@L_END
BEGIN
    SET @OUTPUT=(SUBSTRING (@INPUT,@L_START,1))
    IF @OUTPUT!=@ELIMINATE_CHAR
    BEGIN
        PRINT @OUTPUT
    END
    SET @L_START=@L_START+1
END
赞(0）分享  回复(0）举报  2021-07-24
hpxqektj

hpxqektj8#

很简单，你可以通过下面的查询：

DECLARE @str NVARCHAR(MAX)='ControlID_05436b78-04ba-9667-fa01-9ff8c1b7c235,3'
SELECT LEFT(@str, CHARINDEX(',',@str)-1),RIGHT(@str,LEN(@str)-(CHARINDEX(',',@str)))
赞(0）分享  回复(0）举报  2021-07-24
vlf7wbxs

vlf7wbxs9#

我的表格：

Value  ColOne
--------------------
1      Cleo, Smith

如果没有太多的列，下面的操作应该可以

ALTER TABLE mytable ADD ColTwo nvarchar(256);
UPDATE mytable SET ColTwo = LEFT(ColOne, Charindex(',', ColOne) - 1);
--'Cleo' = LEFT('Cleo, Smith', Charindex(',', 'Cleo, Smith') - 1)
UPDATE mytable SET ColTwo = REPLACE(ColOne, ColTwo + ',', '');
--' Smith' = REPLACE('Cleo, Smith', 'Cleo' + ',')
UPDATE mytable SET ColOne = REPLACE(ColOne, ',' + ColTwo, ''), ColTwo = LTRIM(ColTwo);
--'Cleo' = REPLACE('Cleo, Smith', ',' + ' Smith', '')

结果：

Value  ColOne ColTwo
--------------------
1      Cleo   Smith
赞(0）分享  回复(0）举报  2021-07-24
4dc9hkyq

4dc9hkyq10#

问题很简单，但问题很热门：）
所以我为string\u split（）创建了一些 Package 器，它以更一般的方式产生pivot结果。它是一个返回值的表函数（nn，value1，value2，值50）-对于大多数csv行足够了。如果有更多的值，它们将换行到下一行-nn表示行号。将第三个参数@columncnt=[yournumber]设置为在特定位置换行：

alter FUNCTION fn_Split50
(   
    @str varchar(max),
    @delim char(1), 
    @columnCnt int = 50
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT * 
    FROM (SELECT 
            nn = (nn - 1) / @columnCnt + 1, 
            nnn = 'value' + cast(((nn - 1) % @columnCnt) + 1 as varchar(10)), 
            value
        FROM (SELECT 
            nn = ROW_NUMBER() over (order by (select null)),
            value
            FROM string_split(@str, @delim) aa
            ) aa
        where nn > 0
    ) bb
    PIVOT  
    (  
    max(value) 
    FOR nnn IN (    
        value1, value2, value3, value4, value5, value6, value7, value8, value9, value10,
        value11, value12, value13, value14, value15, value16, value17, value18, value19, value20,
        value21, value22, value23, value24, value25, value26, value27, value28, value29, value30,
        value31, value32, value33, value34, value35, value36, value37, value38, value39, value40,
        value41, value42, value43, value44, value45, value46, value47, value48, value49, value50        
     )  
    ) AS PivotTable 
)

使用示例：

select * from dbo.fn_split50('zz1,aa2,ss3,dd4,ff5', ',', DEFAULT)

select * from dbo.fn_split50('zz1,aa2,ss3,dd4,ff5,gg6,hh7,jj8,ww9,qq10', ',', 3)

select * from dbo.fn_split50('zz1,11,aa2,22,ss3,33,dd4,44,ff5,55,gg6,66,hh7,77,jj8,88,ww9,99,qq10,1010', ',',2)


希望能有所帮助：）

赞(0）分享  回复(0）举报  2021-07-24
hm2xizp9

hm2xizp911#

我遇到了一个类似的问题，但一个复杂的一个，因为这是第一个线程，我发现关于这个问题，我决定张贴我的发现。我知道这是一个简单问题的复杂解决方案，但我希望我可以帮助其他人谁去寻找一个更复杂的解决方案这个线程。我不得不拆分一个包含5个数字（列名：levelsfeed）的字符串，并在单独的列中显示每个数字。例如：8,1,2,2,2应显示为：

1  2  3  4  5
-------------
8  1  2  2  2

解决方案1：使用xml函数：这是迄今为止最慢的解决方案

SELECT Distinct FeedbackID, 
, S.a.value('(/H/r)[1]', 'INT') AS level1
, S.a.value('(/H/r)[2]', 'INT') AS level2
, S.a.value('(/H/r)[3]', 'INT') AS level3
, S.a.value('(/H/r)[4]', 'INT') AS level4
, S.a.value('(/H/r)[5]', 'INT') AS level5
FROM (            
    SELECT *,CAST (N'<H><r>' + REPLACE(levelsFeed, ',', '</r><r>')  + '</r> </H>' AS XML) AS [vals]
    FROM Feedbacks 
)  as d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

解决方案2：使用split函数和pivot(split函数将字符串拆分为具有列名（data）的行

SELECT FeedbackID, [1],[2],[3],[4],[5]
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY feedbackID ORDER BY (SELECT  null)) as rn 
FROM (
    SELECT FeedbackID, levelsFeed
    FROM Feedbacks 
) as a
CROSS APPLY dbo.Split(levelsFeed, ',')
) as SourceTable
PIVOT
(
    MAX(data)
    FOR rn IN ([1],[2],[3],[4],[5])
)as pivotTable

解决方案3：使用字符串操作函数-比解决方案2更快

SELECT FeedbackID,
SUBSTRING(levelsFeed,0,CHARINDEX(',',levelsFeed)) AS level1,
PARSENAME(REPLACE(SUBSTRING(levelsFeed,CHARINDEX(',',levelsFeed)+1,LEN(levelsFeed)),',','.'),4) AS level2,
PARSENAME(REPLACE(SUBSTRING(levelsFeed,CHARINDEX(',',levelsFeed)+1,LEN(levelsFeed)),',','.'),3) AS level3,
PARSENAME(REPLACE(SUBSTRING(levelsFeed,CHARINDEX(',',levelsFeed)+1,LEN(levelsFeed)),',','.'),2) AS level4,
PARSENAME(REPLACE(SUBSTRING(levelsFeed,CHARINDEX(',',levelsFeed)+1,LEN(levelsFeed)),',','.'),1) AS level5
FROM Feedbacks

因为levelsfeed包含5个字符串值，所以我需要对第一个字符串使用substring函数。
我希望我的解决方案能帮助其他人找到更复杂的拆分为列的方法

赞(0）分享  回复(0）举报  2021-07-24
a0zr77ik

a0zr77ik12#

此功能最快：

CREATE FUNCTION dbo.F_ExtractSubString
(
  @String VARCHAR(MAX),
  @NroSubString INT,
  @Separator VARCHAR(5)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @St INT = 0, @End INT = 0, @Ret VARCHAR(MAX)
    SET @String = @String + @Separator
    WHILE CHARINDEX(@Separator, @String, @End + 1) > 0 AND @NroSubString > 0
    BEGIN
        SET @St = @End + 1
        SET @End = CHARINDEX(@Separator, @String, @End + 1)
        SET @NroSubString = @NroSubString - 1
    END
    IF @NroSubString > 0
        SET @Ret = ''
    ELSE
        SET @Ret = SUBSTRING(@String, @St, @End - @St)
    RETURN @Ret
END
GO

用法示例：

SELECT dbo.F_ExtractSubString(COLUMN, 1, ', '),
       dbo.F_ExtractSubString(COLUMN, 2, ', '),
       dbo.F_ExtractSubString(COLUMN, 3, ', ')
FROM   TABLE
赞(0）分享  回复(0）举报  2021-07-24
vfhzx4xs

vfhzx4xs13#

使用instring函数：）

select Value, 
       substring(String,1,instr(String," ") -1) Fname,  
       substring(String,instr(String,",") +1) Sname 
from tablename;

使用了两种功能，

  1. substring(string, position, length) ==>返回从位置到长度的字符串
  2. instr(string,pattern) ==>返回图案的位置。
    如果我们在子字符串中不提供长度参数，它将返回到字符串的末尾
赞(0）分享  回复(0）举报  2021-07-24
oprakyz7

oprakyz714#

使用parsename（）函数

with cte as(
    select 'Aria,Karimi' as FullName
    Union
    select 'Joe,Karimi' as FullName
    Union
    select 'Bab,Karimi' as FullName
)

SELECT PARSENAME(REPLACE(FullName,',','.'),2) as Name, 
       PARSENAME(REPLACE(FullName,',','.'),1) as Family
    FROM cte

结果

Name    Family
-----   ------
Aria    Karimi
Bab     Karimi
Joe     Karimi
赞(0）分享  回复(0）举报  2021-07-24
sulc1iza

sulc1iza15#

可以使用表值函数 STRING_SPLIT ，仅在兼容级别130下可用。如果您的数据库兼容级别低于130，sql server将无法找到并执行 STRING_SPLIT 功能。可以使用以下命令更改数据库的兼容级别：

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

语法

SELECT * FROM STRING_SPLIT ( string, separator )

请参阅此处的文档

赞(0）分享  回复(0）举报  2021-07-24
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com