how to compare an array of values with one value

Question

0.00/5 (No votes)

See more:

hi,

i need to compare the values which i will pass from my end then i want to compare the values with an array

using this SELECT SUBSTRING( (SELECT ',' + CONVERT(varchar, Applicant_Member_Id) FROM Savings_Account_Applicant SAA,Savings_Account SA WHERE SA.ID=SAA.ID AND SA.Account_Operation_Type = 2 FOR XML PATH('')),2,100000) AS CSV i am getting array of values 41,46,46,41,41,41,41,39,38,202,202,205,205,150,150,150,150,41,41,187 but in my procedure i will give only one value as input ie,id=41 for this i want to check the id value is existed in that array or not

Posted 7-Nov-12 1:21am

sk. maqdoom ali

Updated 7-Nov-12 1:36am

v2

Add a Solution

Comments

Om Prakash Pant 7-Nov-12 7:29am

not clear, can you please elaborate?

sk. maqdoom ali 7-Nov-12 7:33am

using this SELECT SUBSTRING(
(SELECT ',' + CONVERT(varchar, Applicant_Member_Id) FROM Savings_Account_Applicant SAA,Savings_Account SA WHERE SA.ID=SAA.ID AND SA.Account_Operation_Type = 2
FOR XML PATH('')),2,100000) AS CSV
i am getting array of values 41,46,46,41,41,41,41,39,38,202,202,205,205,150,150,150,150,41,41,187

but in my procedure i will give only one value as input ie,id=41 for this i want to check the id value is existed in that array or not

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Shanalal Kasim · Answer 1 · 2012-11-07T01:43:00

You can convert your array to table use 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

Sample code:

SQL

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

Output: 
Data
1
2
3
4

for comparing

SQL

select * from [dbo].[String_Tokenizer]('1,2,3,4',',') as Tmp where data=2 -- your id

damodara naidu betha · Answer 2 · 2012-11-07T02:36:00

We can achieve this by using XQuery. For this we need to create a function like following..

SQL

CREATE FUNCTION [dbo].[Splitter](
    @CSVData nvarchar(max),
    @SplitOn nvarchar(5)
)
RETURNS @SplitterValues table 
(
	Data INT
)
AS 
BEGIN
  DECLARE @Xparam XML;

  SELECT @Xparam = CAST('<i>' + REPLACE(@CSVData,@SplitOn,'</i><i>') 
  +  '</i>' AS XML) 

  INSERT INTO @SplitterValues SELECT x.i.value('.','INT')  FROM 
  @Xparam.nodes('//i') x(i)

  RETURN

END

Now we have to use this in query..

SQL

 DECLARE @Id INT;
 DECLARE @CsvData VARCHAR(MAX)

 SET @ID = 41;
 SET @CsvData = '41,46,46,41,41,41,41,39,38,202,202,205,205,150,150,150,150,41,41,187'

 SELECT 1 WHERE @Id IN
(SELECT Data FROM [dbo].[Splitter](@CsvData,','))

I hope this answer helps you..

Thank you..

KiranKumar Roy · Answer 3 · 2012-11-07T01:49:00

Solution 2

check this one
http://www.codeproject.com/Messages/4397095/Re-Array-Contains.aspx[^]

Posted 7-Nov-12 1:49am

KiranKumar Roy

how to compare an array of values with one value

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0