A way to split in sqlserver

Is there a way to split in sql server nvarchar looking like this:

'some text[tag1][tag2]    [tag3]'

into:

[tag1]
[tag2]
[tag3]

?

p.s. I've update example data to show, that there's no strict separator. 开发者_StackOverflow社区I need to get everything inside brackets!

Try below.

declare @v varchar(1000)
set @v = '[1212][12121212]        [[['

create table #temp
(
    v varchar(1000)
)
--insert into #temp(v)values(@v)

declare @Firstindex int
declare @Secondindex int

declare @subval varchar(100)

Set @Firstindex = charindex('[', @v, 1)
while(@Firstindex <> 0)
Begin
    Set @Firstindex = charindex('[', @v, @Firstindex)

    if(@Firstindex = 0)
        break

    Set @Secondindex = charindex(']', @v, @Firstindex)

    if(@Secondindex = 0)
        break;
    if(@Firstindex + 1 <> @Secondindex) 
    Begin
        set @subval = substring(@v, @Firstindex + 1, (@Secondindex - 1) - (@Firstindex ))
        select @subval
        Insert into #temp values(@subval)
    End
    set @Firstindex = @Secondindex

End

select * from #temp
drop table #temp

You can use the following function

CREATE FUNCTION [dbo].[fnSplit](  
    @sInputList VARCHAR(8000)   
  , @sDelimiter VARCHAR(8000) = ','   
) RETURNS @List TABLE (ID VARCHAR(8000))  

BEGIN  
DECLARE @sItem VARCHAR(8000)  
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0  
 BEGIN  
 SELECT  
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),  
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))  

 IF LEN(@sItem) > 0  
  INSERT INTO @List SELECT @sItem  
 END  

IF LEN(@sInputList) > 0  
 INSERT INTO @List SELECT @sInputList   
RETURN  
END  

The output can be verified like

select * from dbo.fnSplit('[12] [12] [13]',' ')

It will show

12
12
13