A way to split in sqlserver

Question

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. I need to get everything inside brackets!

Why would you want to manipulate the data at that level? do it at the code level... — Chris Dowdeswell, May 05 '11 at 11:23
@Chris Dowdeswell : because I want to. I'm writing import stored procedure that has to do smtgh depending on these tags. — IamDeveloper, May 05 '11 at 11:25
Possible duplicate of http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql — Andrew Savinykh, May 05 '11 at 11:26
Ok so are you trying to put a carriage return after each in the field? I'm just not sure what your logic is for doing this considering the tag data are already well formed / delimited with brackets? — Chris Dowdeswell, May 05 '11 at 11:29

score 1 · Accepted Answer · answered May 05 '11 at 11:53

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

and we have a winner!:) Thank you! – IamDeveloper May 05 '11 at 12:06 — IamDeveloper, May 05 '11 at 12:06

Pankaj · Answer 2 · 2011-05-05T11:27:40.827

0

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

edited May 05 '11 at 11:27

answered May 05 '11 at 11:24

Pankaj

9,749
32
139
283

So you mean the data will be strictly between opening and closing square brackets ? – Pankaj May 05 '11 at 11:32
I have posted an another answer. Please follow it. – Pankaj May 05 '11 at 11:56

A way to split in sqlserver

2 Answers2