Click here to Skip to main content
12,507,674 members (56,352 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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 8-Nov-12 18:29pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Create this function in your database

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
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!
:)
  Permalink  
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
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'
sk. maqdoom ali 9-Nov-12 4:18am
   
madam shall i ask one qsn??
sk. maqdoom ali 9-Nov-12 4:19am
   
how much experience do u have in db??
Aarti Meswania 9-Nov-12 5:15am
   
2.5 yrs
and you are posing question many time but each time you have some different requirement
where do you stuck?

what you exactly want?
you had said earlier that you want that Applicant should not be include in more than one joined account which is quite simple just count(*)
and now,
you are searching that applicant combination should not be repeat?

tell us your exact requirement so, that this issue will be solved easily
:)
sk. maqdoom ali 9-Nov-12 5:20am
   
I am sorry for that actually the problem is with my LEAD.like this for every time he is asking me to do in different way without giving me proper requirement.
Aarti Meswania 9-Nov-12 5:27am
   
just ask him
one Applicant can have multiple joined accounts?
if he say no
then just fire query
select case when count(*)>0 then 'Exist' else 'Available' end as Response from Savings_Account_Applicant where Applicant_Member_Id = ...
sk. maqdoom ali 9-Nov-12 5:41am
   
what you send queries before are very useful to me from those two as i am mentioning below

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
sk. maqdoom ali 9-Nov-12 6:23am
   
hello
Aarti Meswania 9-Nov-12 6:28am
   
first is A function
second is Store procedure that you have create, I have just include lines which are highlighted by underline. (in that underlined portion I have use function 'fn_Splitter'

you have problem @Ids is not in ascending order so that Underlined lines will do whole process to make @Ids in asc order,

now use it and tell me if any doubt okay? :)
sk. maqdoom ali 9-Nov-12 6:31am
   
i am writing the whole in Add your solution here
please check it,

Aarti Meswania 9-Nov-12 6:36am
   
what you mean to say?
"i am writing the whole in Add your solution here ... please check it"???
sk. maqdoom ali 9-Nov-12 5:45am
   
i think i am clear??
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Create below function

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:

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

Output:
Data
1
2
3
4
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

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


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'
  Permalink  
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
Aarti Meswania 9-Nov-12 7:13am
   
Did it worked or not???
sk. maqdoom ali 9-Nov-12 7:22am
   
its working fine just now i explained this to my LEAD

thanks a lot madam
u helped me a lot:
thank u
sk. maqdoom ali 9-Nov-12 7:25am
   
in which organization u r working??
Aarti Meswania 9-Nov-12 7:26am
   
okay finally it been solved
now, mark it as answer so,
other persons stop wasting time on this problem It is good habit.

sk. maqdoom ali 9-Nov-12 8:09am
   
madam

in which organization u r working??
Aarti Meswania 9-Nov-12 8:15am
   
I am windows application developer in ahmedabad gujarat
sk. maqdoom ali 9-Nov-12 8:17am
   
i am sql developer , Hyderabad
Aarti Meswania 9-Nov-12 8:19am
   
okay how much experience do you have?
and in which technology you work ASP.net or windows application in .net?
sk. maqdoom ali 9-Nov-12 8:21am
   
i have 8 months experience

i joined as a DBA
i will work on database only
Aarti Meswania 9-Nov-12 8:25am
   
good best of luck
sk. maqdoom ali 9-Nov-12 8:28am
   
thank u,,

happy weekend
Aarti Meswania 9-Nov-12 8:32am
   
welcome :)
Happy weekends
sk. maqdoom ali 9-Nov-12 8:34am
   
hmm....
sk. maqdoom ali 11-Nov-12 23:45pm
   
hi , gd mrng
sk. maqdoom ali 14-Nov-12 4:21am
   
hi,
shall we declare
output return value in the returns clause in functions??
sk. maqdoom ali 14-Nov-12 5:36am
   
busy??

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160927.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100