CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2000
Sree
Sree

Reputation: 584

Split function getting error

Below code i am using for MSSQL 2005,but when i try to run in MSSQL 2000 i am getting below erros. 

Msg 156, Level 15, State 1, Procedure StrSplit, Line 5
Incorrect syntax near the keyword 'WITH'.
Msg 170, Level 15, State 1, Procedure StrSplit, Line 15
Line 15: Incorrect syntax near ')'.

CREATE FUNCTION dbo.StrSplit (@sep char(1), @s varchar(512))  
    RETURNS table  
    AS  
    RETURN (  
        WITH Pieces(pn, start, stop) AS (  
          SELECT 1, 1, CHARINDEX(@sep, @s)  
          UNION ALL  
          SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)  
          FROM Pieces  
          WHERE stop > 0  
        )  
        SELECT pn,  
          SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s  
        FROM Pieces  
      )

Thanks in advance

Upvotes: 1

Views: 745

Answers (3)

Praveen
Praveen

Reputation: 1449

Use the below function in its place.

CREATE  FUNCTION [dbo].[fn_Split](@text VARCHAR(8000), @delimiter VARCHAR(20) = ' ')
RETURNS @Strings TABLE
(    
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)   
)
AS

BEGIN
DECLARE @index int 

SET @text = LTRIM(RTRIM(@text))

SET @index = -1 
WHILE (LEN(@text) > 0) 
 BEGIN  
    SET @index = CHARINDEX(@delimiter , @text)  
    IF (@index = 0) AND (LEN(@text) > 0)  
      BEGIN   
        INSERT INTO @Strings VALUES (@text)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   
        SET @text = RIGHT(@text, (LEN(@text) - @index))  
      END  
    ELSE 
      SET @text = RIGHT(@text, (LEN(@text) - @index)) 
    END
  RETURN
END

Hope this helps!!

Upvotes: 1

DaveShaw
DaveShaw

Reputation: 52798

You cannot use CTE's (Common Table Expressions - WITH) in SQL Server 2000. They were introduced in SQL Server 2005.

Upvotes: 1

mservidio
mservidio

Reputation: 13057

CTE's - IE "WITH" are a feature starting in sql 2005. Not available in 2000...

Upvotes: 1

Related Questions