Click here to Skip to main content
15,942,710 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
hi,

how to insert string data into a table variable

ex: string= '41,21,23,25'

i want to insert the above ID into a table variable as below

ID
41
21
23
25
Posted

SQL
ALTER Function [dbo].[fn_Splitter] (@IDs Varchar(100))  
Returns @Tbl_IDs Table (ID Int)  As  
Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
  -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1
 While @Pos1<len(@ids)>
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return  
 End



SQL
ALTER FUNCTION [dbo].[Check_MemberIDCombination]
 (
  @Ids VARCHAR(1000)
  ) 
 RETURNS VARCHAR(20)
AS 
BEGIN
 
DECLARE @Msg VARCHAR(20);
declare @count int
 
select @Ids=(select convert(varchar,Id) + ',' from (select Id from dbo.Fn_Splitter(@Ids)) as tmp order by Id asc for xml path (''))
select @Ids= (select substring(@Ids,1,Len(@Ids)))

--DECLARE @Computed table(id nvarchar(1000),Member_Id nvarchar(1000))
--insert into @computed
set @count = (select count(*) from
(
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) strings from
(
    select ID ,
    (select convert(varchar, Distinct Applicant_Member_Id) + ',' as [text()] from Savings_Account_Applicant where ID =saa.ID order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
    from Savings_Account_Applicant saa
    group by ID
) as Savings_Account ) tab1
where tab1.strings=@Ids)
 
 --= ( select count(*) from @Computed where Member_Id=@Ids)
 
if @count>0
begin
 
 SET @Msg = 'Already Exists'
end 
ELSE
 
 BEGIN
 SET @Msg = 'Available'
 
END
RETURN @Msg 
END



now i am executing it by as below input

select dbo.Check_MemberIDCombination('41,51') as Msg

the above '150,150,150,150' is already existed in the table

but here i am getting the Msg as 'Available' instead of 'Already Exists'
 
Share this answer
 
v2
Comments
Aarti Meswania 9-Nov-12 6:39am    
what is it '150,150,150,150' ?

please give inputs
some rows of computed Query
sk. maqdoom ali 9-Nov-12 6:45am    
id strings
18 39,39,39,39,51
102 41,51
113 41,116
155 39,217
793 150,150,150,150

sk. maqdoom ali 9-Nov-12 6:47am    
select dbo.Check_MemberIDCombination('41,51') as Msg

the above '41,51' is already existed in the table

but here i am getting the Msg as 'Available' instead of 'Already Exists'
Aarti Meswania 9-Nov-12 6:57am    
see updated solution first.

it is not good 4 times it shows 150 in last line and 39 in first line
Aarti Meswania 9-Nov-12 7:00am    
change this line
select @Ids= substring(@Ids,1,Len(@Ids))

with
select @Ids= substring(@Ids,1,Len(@Ids) -1 )

you will get result
Create below function

SQL
CREATE FUNCTION [dbo].[String_Tokenizer]
(
	@RowData nvarchar(max),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	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


The above function will return a temporary table.

Sample code:

SQL
select * from [dbo].[String_Tokenizer]('1,2,3,4',',')


Output:
Data
1
2
3
4
 
Share this answer
 
Create this function in your database

SQL
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[fn_Splitter] (@IDs Varchar(100) )  
Returns @Tbl_IDs Table  (ID Int)  As  
Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
  -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1
 While @Pos1<len(@ids)
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return
End



Now Your Store procedure should be like this
SQL
ALTER FUNCTION [dbo].[Check_MemberIDCombination]
 (
  @Ids VARCHAR(1000)
  ) 
 RETURNS VARCHAR(20)
AS 
BEGIN
 
DECLARE @Msg VARCHAR(20);
declare @count int
 
select @Ids=(select convert(varchar,Id) + ',' from ((select Id from dbo.Fn_Splitter(@Ids))) as tmp order by Id for xml path (''))
select @Ids= substring(@Ids,1,Len(@Ids))


DECLARE @Computed table(id nvarchar(1000),Member_Id nvarchar(1000))
insert into @computed
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
    select ID ,
    (select convert(varchar, Applicant_Member_Id) + ',' as [text()] from Savings_Account_Applicant where ID =saa.ID order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
    from Savings_Account_Applicant saa
    group by ID
) as Savings_Account ORDER BY ID
 
set @count = ( select count(*) from @Computed where Member_Id=@Ids)

if @count>0
begin

 SET @Msg = 'Already Exists'
end 
ELSE
 begin
 SET @Msg = 'Available'
 
END
RETURN @Msg 
end


Happy Coding!
:)
 
Share this answer
 
v2
Comments
sk. maqdoom ali 9-Nov-12 1:19am    
For the execution of dbo.[fn_Splitter]

it shows an error for the below line

While @Pos1<len(@ids)>

Msg 102, Level 15, State 1, Procedure fn_Splitter, Line 12
Incorrect syntax near '>'.
sk. maqdoom ali 9-Nov-12 1:21am    
its just

While @Pos1
Aarti Meswania 9-Nov-12 1:23am    
see updated solution while copy paste data it includes some symbols and tags it self
sk. maqdoom ali 9-Nov-12 3:51am    
using this [dbo].[fn_Splitter] function shall we split the data into ascending order as input??
ex:24,35,78,45
into
24,35,45,78
to give input to [dbo].[Check_MemberIDCombination]
sk. maqdoom ali 9-Nov-12 4:11am    
my requirement is
i have to convert string data values which passed as input into ascending order
and
the data which is return from the following query
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
select ID ,
(select convert(varchar, Applicant_Member_Id) + ',' as [text()] from Savings_Account_Applicant where ID =saa.ID order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account ORDER BY ID

and compare both of this if any combination is existed then 'Already Exists' else 'Available'
SQL
CREATE FUNCTION DBO.FUN_SPLIT
(
	@DATA VARCHAR(2000),	
	@SEP VARCHAR(5)
)  
RETURNS @TEMP TABLE (ID INT IDENTITY(1,1), DATA NVARCHAR(100)) 
AS  
/************************************************************************************** TESTING			:   SELECT * FROM DBO.FUN_SPLIT('A,B,C',',')
**************************************************************************************/
BEGIN 	
	DECLARE @CNT INT	
	SET @CNT = 1	
	WHILE (CHARINDEX(@SEP,@DATA)>0)
	BEGIN		
		INSERT INTO @TEMP (DATA)		
		SELECT	DATA = LTRIM(RTRIM(SUBSTRING(@DATA,1,CHARINDEX(@SEP,@DATA)-1)))		
		SET @DATA = SUBSTRING(@DATA,CHARINDEX(@SEP,@DATA)+1,LEN(@DATA))		
		SET @CNT = @CNT + 1	
	END		
	INSERT INTO @TEMP (DATA)	
	SELECT DATA = LTRIM(RTRIM(@DATA))	
	RETURN

END



I hope this will help u. If helped Vote it.
Thanks
 
Share this answer
 
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900