如何给出以逗号分隔的3个值作为输入 [英] how to give 3 values separated by comma as input

问题描述

如何传递1,2,3，----作为ID的输入

和

如何将它们提取为invidual来检查mytable

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

推荐答案

列中存在的不同组合.
首先创建一个像下面这样的函数...

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

 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....

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

谢谢

Thank you

不像您想的那么简单:

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

创建以下函数

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

这篇关于如何给出以逗号分隔的3个值作为输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！