使用[]在SQL Server中拆分逗号分区字符串 [英] Split a comma sperated string in SQL server with []

问题描述

select distinct val from dbo.Split('[Country],[Created By],[Date of Birth],[High School],[If Other, What Country?],[If Other- What Country?],[Middle Name],[Preferred First Name],[Sex]',',')

这是我用逗号分割的函数。

This is my function that splits by comma.

CREATE FUNCTION [dbo].[Split]    
(        
  @delimited nvarchar(max),        
  @delimiter nvarchar(100)        
) RETURNS @t TABLE        
(        
  val nvarchar(max)        
)        
AS        
BEGIN        
  declare @xml xml        
  set @xml = N'<root><r><![CDATA[' + replace(@delimited,@delimiter,']]></r><r><![CDATA[') + ']]></r></root>'        
        
  insert into @t(val)        
  select        
    r.value('.','varchar(max)') as item        
  from @xml.nodes('//root/r') as records(r)        
        
  RETURN        
END

我想把[abc]，[cde，cdf]

之外的字符串分成abc和cde，cdf但是分成



abc，

cde和cdf



我尝试过：



请帮助。

I want to split the string outside the [abc],[cde,cdf]
into abc and cde,cdf but its splitting into

abc,
cde and cdf

What I have tried:

Please help.

Split a comma sperated string in Sql server with []

推荐答案

在上拆分，在Sql server中拆分逗号分区字符串而不是逗号，替换 [并在项目开头处理逗号...如果它存在...

Split on ] instead of comma, replace the [ and handle the comma at the start of an item if it exists...

DECLARE @x nvarchar(max) = '[abc],[cde,cdf]'
;with q as
(
	select REPLACE(val, '[','') as val2
	from dbo.Split(@x, ']')
	where isnull(val,'') <> ''
)
select case when SUBSTRING(val2,1,1)=',' THEN SUBSTRING(val2, 2, LEN(val2))
		else val2 END
from q

我只使用CTE（或子查询会这样做）来避免多次调用split函数。

I only used a CTE (or a sub-query would do it) to avoid multiple calls to the split function.

这篇关于使用[]在SQL Server中拆分逗号分区字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！