You can use the fnsplitter function which is used widely for splitting in t sql... you have to add this function to your database...
USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Function [dbo].[fnSplitter] (@IDs Varchar(4000) )
Returns @Tbl_IDs Table (ID nvarchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS ) As
Begin
Set @IDs = @IDs + ','
Declare @Pos1 Int
Declare @Pos2 Int
Set @Pos1=1
Set @Pos2=1
While @Pos1<len(@ids)>
Begin
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs Select Substring(@IDs,@Pos2,@Pos1-@Pos2)
Set @Pos2=@Pos1+1
Set @Pos1 = @Pos1+1
End
Return
End
after adding change your query to
Declare @val nvarchar(max)
Set @val= 'E1+E2+E3'
Set @val=REPLACE(@val,'E','')
Set @val=REPLACE(@val,'+',',')
Select * from empmas where headcode in(Select ID From fnSplitter(@Val))