how to give 3 values separated by comma as input

Question

0.00/5 (No votes)

See more:

hi,

how to pass 1,2,3,---- as input for ID

and

how to extract them into invidual to check the different combinations existed in the column of mytable

Posted 7-Nov-12 20:46pm

sk. maqdoom ali

Add a Solution

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)

damodara naidu betha · Answer 1 · 2012-11-07T22:51:00

Solution 3

Try this.
First create a function like bellow...

SQL

 CREATE FUNCTION [dbo].[CheckCombination]
 (
  @Ids VARCHAR(100)
  ) 
 RETURNS VARCHAR(20)
AS 
BEGIN

DECLARE @Msg VARCHAR(20);

IF EXISTS ( SELECT 1 FROM (
 SELECT ID,STUFF(( SELECT ',' + CAST(ApplicantId AS VARCHAR) FROM ApplicantTable 
 WHERE ID = A.Id FOR XML PATH('') ),1,1,'')Ids
 FROM ApplicantTable A GROUP BY ID ) A WHERE Ids = @Ids )

 SET @Msg = 'Exists'

ELSE

 SET @Msg = 'Not Exists'

RETURN @Msg
END

After creation, test the function....

SQL

SELECT dbo.CheckCombination('1,2,3')

Thank you

Posted 7-Nov-12 22:51pm

damodara naidu betha

Updated 7-Nov-12 22:52pm

v2

Comments

sk. maqdoom ali 8-Nov-12 5:27am

thank u , but here one problem is
i already inserted
ID Applicant_Member_id
18 39,39,39,51,39
102 41,51
155 39,217

now again i am giving 51,41 for any ID then this must not be display as 'Notexists'
becoz the 51,41 is already existed.
here we changed the order only not numbers.
how can we come across this problem

sk. maqdoom ali 8-Nov-12 5:50am

sir , how can we check if there is any existed value in combination for this

sk. maqdoom ali 8-Nov-12 5:59am

i used this function for checking the combination of existed values but its is not checking if the combination is reversed etc

CREATE FUNCTION [dbo].[Check_MemberIDCombination]
(
@Ids VARCHAR(1000)
)
RETURNS VARCHAR(20)
AS
BEGIN

DECLARE @Msg VARCHAR(20);

IF EXISTS ( SELECT 1 FROM (
SELECT ID,STUFF(( SELECT ',' + CAST(Applicant_Member_Id AS VARCHAR) FROM Savings_Account_Applicant
WHERE ID = A.Id FOR XML PATH('') ),1,1,'')Ids
FROM Savings_Account_Applicant A GROUP BY ID ) A WHERE Ids = @Ids )

SET @Msg = 'Already Exists'

ELSE

SET @Msg = 'Available'

RETURN @Msg
END

OriginalGriff · Answer 2 · 2012-11-07T21:11:00

Solution 1

Not as simple as you might think:

SQL

DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = '2,3,177,'
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ','
CREATE TABLE #tempTab (id int not null)
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO #tempTab (id) VALUES (@VALUE)
END
SELECT * FROM myTable WHERE id IN (SELECT id FROM #tempTab)
DROP TABLE #tempTab

Posted 7-Nov-12 21:11pm

OriginalGriff

Comments

sk. maqdoom ali 8-Nov-12 3:24am

yes, u r correct
i have a table with ID , Applicant_member_Id columns
when we are inserting one or more Applicant_member_Id's for ex:39,41,116 as input then i have to check entire this combination 39,41,116 is already existed for any particular ID in the table. if the combination is existed then we display "Already Existed' else 'available'

sk. maqdoom ali 8-Nov-12 3:34am

tell me sir, how can i check this combination of values based on ID in a table??

OriginalGriff 8-Nov-12 3:59am

So, is Applicant_member_Id a string containing comma separated values? Or a number? I think you need to give me a bit more info here! (Remember I can't see your screen, or read your HDD)

sk. maqdoom ali 8-Nov-12 4:06am

yes sir , Applicant_member_Id is a string containing comma separated value
ex:input=41,42,43,---
i have to check any ID in my table having this combination 41,42,43
if this type of combination is existed then dislay msg as 'Already Existed'
else we can insert

sk. maqdoom ali 8-Nov-12 4:08am

in my table Applicant_member_Id is a column having only a single value for particular ID
ex:101 41
102 42
103 41
- -
- -

sk. maqdoom ali 8-Nov-12 4:10am

for my application i have give 2 or more values to check for existence in ID column

OriginalGriff 8-Nov-12 4:28am

So you are trying to find out if a a row exists containing 41, and a row exists containing 42, and a row exists containing 43?
If so, you can do it, but it would probably be easier to do it in the code you are returning the data to.
The problem is that your example shows both 101 and 103 having the same Applicant_member_Id so you can't just use the rows returned count. (I'm assuming that the Id column contains unique values)

sk. maqdoom ali 8-Nov-12 4:35am

initially they are inserted the data like that but now we are inserting at a time more than two values , in that values we have to check any combination is existed for a any ID of a table:
i written the below query that return data
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 for xml path('')) as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account

from this above query i separated
ID Applicant_Member_id
18 39,39,39,51,39
102 41,51
155 39,217

OriginalGriff 8-Nov-12 5:00am

So why can't you just compare the two strings?

sk. maqdoom ali 8-Nov-12 7:37am

i used this function for checking the combination of existed values but its is not checking if the combination is reversed etc CREATE FUNCTION [dbo].[Check_MemberIDCombination] ( @Ids VARCHAR(1000) ) RETURNS VARCHAR(20) AS BEGIN DECLARE @Msg VARCHAR(20); IF EXISTS ( SELECT 1 FROM ( SELECT ID,STUFF(( SELECT ',' + CAST(Applicant_Member_Id AS VARCHAR) FROM Savings_Account_Applicant WHERE ID = A.Id FOR XML PATH('') ),1,1,'')Ids FROM Savings_Account_Applicant A GROUP BY ID ) A WHERE Ids = @Ids ) SET @Msg = 'Already Exists' ELSE SET @Msg = 'Available' RETURN @Msg END

sk. maqdoom ali 8-Nov-12 7:38am

but here one problem is i already inserted ID Applicant_Member_id 18 39,39,39,51,39 102 41,51 155 39,217 now again i am giving 51,41 for any ID then this must not be display as 'Notexists' becoz the 51,41 is already existed. here we changed the order only not numbers. how can we come across this problem

OriginalGriff 8-Nov-12 7:52am

To be honest, I wouldn't do it like that. You have a series of ids as strings, separated by spaces and each separated version is comma separated and unsorted. And yopu wan to check each space separated block individually, as if it was sorted.
That is not nice, and without arrays (which SQL doesn't have), your only real option is to generate a valiarble number of temporary tables to hold the variable components and sort them.

Seriously, I would either rethink my data storage or do this in "proper" code rather than trying to do it in SQL. While you could do it in SQL, I think the result would be horribly difficult to maintain, not just to write!

Shanalal Kasim · Answer 3 · 2012-11-07T21:50:00

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