Database Related

Q !: Create A Split Function In databse

Ans:

GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSplitString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'Create  FUNCTION [dbo].[fnSplitString]
(
 @RowData varchar(8000),
 @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
 Id int identity(1,1),
 Data nvarchar(100)
) 
AS  
BEGIN 
 Declare @Cnt int
 Set @Cnt = 1

 While (Charindex(@SplitOn,@RowData)>0)
 Begin
  Insert Into @RtnValue (data)
  Select 
   Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
  Set @Cnt = @Cnt + 1
 End

 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))

 Return
END'
END


2) In Query
select * from [dbo].[tblInvoice] where [InvoiceID] in (select Data from fnSplitString('203,202',','))


2) My favourite Is

GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE function [dbo].[fun_Split]   
(
  @String varchar(8000),   
  @Delimiter char(1)  
) Returns @Results Table (Items varchar(8000))   
  
  
As      
Begin      
 Declare @Index int      
 Declare @Slice varchar(4000)      
  Select @Index = 1      
  If @String Is NULL Return      
  While @Index != 0      
  Begin      
   
  Select @Index = CharIndex(@Delimiter, @String)      
  If (@Index != 0)      
  Select @Slice = left(@String, @Index - 1)      
  else      
  Select @Slice = @String      
  
  Insert into @Results(Items) Values (@Slice)      
  Select @String = right(@String, Len(@String) - @Index)      
  If Len(@String) = 0 break      
  End      
Return      
End ' 

END

Ouery Wil Be
select * from [dbo].[tblInvoice] where [InvoiceID] in (select * from [dbo].[fun_Split]('203,202',','))

insert statment when not matches column name

insert into [dbo].[tblInvoice] (InvoiceID,Name) 
select  InvoiceID,'Mahesh' as Name from [dbo].[fun_Split]('203,202',','))

Column Name should be same if not then use as and create table same as above  table.