declare @tickers varchar(max)
set @tickers = replicate(cast('abcde,' as varchar(max)),10000);
select * from dbo.fnSNMParseString(',', @tickers)

create function dbo.fnSNMParseString
(
   @separator CHAR,
   @string varchar(max)
)
returns @Result table (row_id int not null primary key, token varchar(8000))
as
begin
   declare @len int; set @len = len(@string); 
   with CTE as
   (
      select
         0 as rowId,
         cast(1 as bigint) as beg,
         charindex(@separator,@string,1) as nextPos,
         cast(null as varchar(max)) as token
      union all
      select
         c.rowId+1 as rowId,
         c.NextPos+1 as beg,
         case when c.nextPos <> 0 then charindex( @separator, @string, c.nextPos+1) else c.nextPos-1 end as nextPos,
         substring(@string,c.beg,case when c.nextPos = 0 then @len else c.nextPos-c.beg end)  as token
      from
         CTE c
      where
         c.nextPos > -1 or c.rowId = 0
   )
   insert into @Result
   select rowId,token from CTE where rowId <> 0
   option (MAXRECURSION 0);
   
   return;
end
GO

quote:

---

Originally posted by sunitabeck

At my last job, I had to parse strings that had of the order of 20,000 comma-separated tokens in it. (Don't ask *groan*, not my fault, not my design) I tried several methods I found on the web, none of which were giving satisfactory performance. So I wrote one of my own using recursive CTE, which worked well - I thought it performed at least an order of magnitude better than those I found on the web. I don't know how this compares with the ones in khtan's links (or if it is even replicating the same algorithm); I don't recall trying those.

Unfortunately, the company folded, and didn't get to take my masterpiece before the doors closed. So I tried to reproduce it from what I recall. Obviously, I have not tested this as well as I tested the real one which died a premature death along with the company and my job. If you are really looking for performance, give it a try, but just be aware that:

a) Not as well-tested as the ones in the links that khtan gave you.

b) I went to great lengths to avoid function calls, casting etc. in the original, but not in this. So performance may not be what it could be.

declare @tickers varchar(max)
set @tickers = replicate(cast('abcde,' as varchar(max)),10000);
select * from dbo.fnSNMParseString(',', @tickers)

-- and the function

create function dbo.fnSNMParseString
(
   @separator CHAR,
   @string varchar(max)
)
returns @Result table (row_id int not null primary key, token varchar(8000))
as
begin
   declare @len int; set @len = len(@string); 
   with CTE as
   (
      select
         0 as rowId,
         cast(1 as bigint) as beg,
         charindex(@separator,@string,1) as nextPos,
         cast(null as varchar(max)) as token
      union all
      select
         c.rowId+1 as rowId,
         c.NextPos+1 as beg,
         case when c.nextPos <> 0 then charindex( @separator, @string, c.nextPos+1) else c.nextPos-1 end as nextPos,
         substring(@string,c.beg,case when c.nextPos = 0 then @len else c.nextPos-c.beg end)  as token
      from
         CTE c
      where
         c.nextPos > -1 or c.rowId = 0
   )
   insert into @Result
   select rowId,token from CTE where rowId <> 0
   option (MAXRECURSION 0);
   
   return;
end
GO

And, like they say in legal documents, I make no warranties or merchantability or fitness for any purpose other than for intellectual curiosity :-)

---

quote:

---

Use the split function written by Pisco.

---

quote:

---

Originally posted by Sachin.Nand

quote:

---

Use the split function written by Pisco.

---

Did you mean Peso ???



PBUH

---