Insert values into table after splitting the string

Question

I want to insert values into employee table. And those values are in string format ~ separated

E.g: AA~B~123

I am splitting it using following function

CREATE FUNCTION [db_owner].[FN_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 

Now I get Output as

SELECT * FROM db_owner.FN_Split('AA~B~123','~')

Output

items
______
AA
B
123

Now I am stuck here

How can I insert above values in employee table???

like

insert into employee (name,add,phone)
values('AA','B','123');

Please guide.

Tried this but not working

insert into employee
SELECT * FROM db_owner.FN_Split('AA~BB~CC','~')

ERROR

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

Solution 1

if you could add a small counter into the stored procedure like this then life would be easier:

CREATE FUNCTION [db_owner].[FN_Split] (@String varchar(8000), @Delimiter char(1))      
   returns @temptable TABLE (orderId int,items varchar(8000))        
   as        
   begin        
       declare @idx int        
       declare @slice varchar(8000)        
       declare @orderId int = 0 --<added a counter

        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(orderId, Items) values(@orderId, @slice)        
           set @orderId = @orderId+1 --<increment the counter

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

Your subsequent query could be something like the following:

DECLARE @name varchar(50) = (SELECT items  FROM db_owner.FN_Split('AA~BB~CC','~') where orderId = 0)
DECLARE @add varchar(50) = (SELECT items  FROM db_owner.FN_Split('AA~BB~CC','~') where orderId = 1)
DECLARE @phone varchar(50) = (SELECT items  FROM db_owner.FN_Split('AA~BB~CC','~') where orderId = 2)
insert into employee 
    (
    name,
    add,
    phone
    )
values
    (
    @name, 
    @add,
    @phone
    )

But have you tried changing the procedure so that it outputs the data in a horizontal format rather than the vertical output that you currently have?

Solution 2

You are using a string split function that returns your items as rows. You need a function that return them as columns instead.

Or you can do it directly in the query. Perhaps something like this.

declare @S varchar(10) = 'AA~B~123'

select left(@S, T1.Pos - 1) as Col1,
       substring(@S, T1.Pos+1, T2.Pos-T1.Pos-1) as Col2,
       substring(@S, T2.Pos+1, len(@S)-T2.Pos) as Col3
from (select charindex('~', @S)) as T1(Pos)
cross apply (select charindex('~', @S, T1.Pos+1)) as T2(Pos)

Result:

Col1       Col2       Col3
---------- ---------- ----------
AA         B          123

Here is a version that works in SQL Server 2000

declare @S varchar(10) 
set @S = 'AA~B~123'

select left(@S, T.Pos1 - 1) as Col1,
       substring(@S, T.Pos1+1, T.Pos2-T.Pos1-1) as Col2,
       substring(@S, T.Pos2+1, len(@S)-T.Pos2) as Col3
from (select T.Pos1,
             charindex('~', @S, T.Pos1+1) as Pos2
      from (select charindex('~', @S) as Pos1) as T
     ) as T

Solution 3

Please try this query:

Insert into employee(col1,col2,col3) 
select substring_index('AA~B~123','~',1) as col1,substring_index(substring_index('AA~B~123','~',-2),'~',1) as col2,
substring_index(substring_index('AA~B~123','~',-1),'~',1) as col3