CODEWITHSUNDEEP
c#sqlsplit
Dirk
Dirk

Reputation:

SQL Split Comma Separated Column on Delimiter

Here is a split function, it can apply as dbo.Split('sf,we,fs,we',','), when I change the string to column name it doesn't work, such as dbo.Split(table.columnName,',').

Select * from dbo.Split('[email protected]','@')

is works but 

Select * from dbo.Split((Select Email from Users),'@')

has an error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','

The Function is here:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split] (@String varchar(8000), @Delimiter char(1))     
   returns @temptable TABLE (items varchar(8000))       
   as       
   begin       
       declare @idx int       
        declare @slice varchar(8000)       

        select @idx = 1       
            if len(@String)<1 or @String is null  return       

       while @idx!= 0       
       begin       
           set @idx = charindex(@Delimiter,@String)       
           if @idx!=0       
               set @slice = left(@String,@idx - 1)       
           else       
              set @slice = @String       

           if(len(@slice)>0)  
               insert into @temptable(Items) values(@slice)       

           set @String = right(@String,len(@String) - @idx)       
           if len(@String) = 0 break       
       end   
   return       
   end

which it's refered to: SQL 2005 Split Comma Separated Column on Delimiter

Or someone can give me a similiar function which can split one column into two

Upvotes: 0

Views: 13346

Answers (1)

shahkalpesh
shahkalpesh

Reputation: 33484

That is because the function is returning a TABLE.

SELECT Split(myColumn) FROM myTable

will mean

SELECT myOutputTableFromSplit FROM myTable

It means, return a table from the table & SQL cannot do that.

Upvotes: 1

Related Questions