如何在列中分隔ID [英] how to separate ID's in column

问题描述

它是一个酒店项目.当用户从表格i中选择多个早餐项目时，像2,3,4,8.这样存储，这里2，3，4，8都是项目的ID.我想分开这些数字来检索项目.

在我的数据库中，其存储如

表1

menuitemsid |菜单名
2,3,4,8 |早餐


表2

商品编号|商品名称
2 | Dosa
3 |披萨
4 | Idly
5 | Muruku




我想要类似
的输出
早餐
-------------
Dosa
披萨
闲适地
Muruku

its an hotel project., when user choose multi items for breakfast items from form i stored like 2,3,4,8 ., here 2 ,3 ,4, 8 are all ID''s of item., now i want to separte these numbers to retrieve items.

in my database its store like

Table 1

menuitemsid|menuname
2,3,4,8 |Breakfast


Table 2

Itemid|item name
2 |Dosa
3 |Pizza
4 |Idly
5 |Muruku




i want output like

Breakfast
-------------
Dosa
Pizza
Idly
Muruku

推荐答案

这有点复杂，因为SQL Server没有数组的概念.
您将必须检索字符串，然后将其分解为ID，并将其用作IN子句的一部分.
此过程执行类似的操作:

That''s a bit complex, because SQL server does not have a concept of arrays.
You will have to retrieve the string, and then break it into ID''s and use that as part of an IN clause.
This procedure does something similar:

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

但是您将对其进行修改，以使其完全适合您的需要.

But you will have modify it to fit exactly what you want.

创建用于分隔ID的函数.

Create Function for separating Ids.

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

这篇关于如何在列中分隔ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！