blocks|key|2587274|text|必须注意:永远不要将用户提供的数据连接到SQL查询中；您将面临SQL注入攻击。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2587275|我之前的回答(使用"IN")是基于一个错误的想法，即您从用户那里收集多个IN，并针对一个值的列进行搜索。但是你做的正好相反--列有多个值，并且你试图匹配来自用户的一个值。|2587276|这是个不好的方法。不要使用逗号分隔的值；添加一个具有一对多关系的新表。然后，查询将类似于|2587277|SQL+=+"SELECT+Product.*+FROM+Product,+ProductCategory+WHERE+ProductCategory.ProductId=Product.ID+AND+ProductCategory.CategoryId+=+"+&+Request.QueryString("CategoryId")|code-block|syntax|javascript|2587278|如果您真的想做逗号分隔的事情，可以使用LIKE和%25通配符进行搜索，但是它是脆弱的(例如，您必须确保ID+"2“与”12“不匹配)。|2587279|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Obligatory note: NEVER concatenate user-supplied data into a SQL query; you are opening yourself up to SQL injection attacks.

My previous answer (using "IN") was based on a mistaken idea that you were collecting multiple IDs from the user, and searching against a column with one value. But you're doing the opposite -- the column has multiple values, and you're trying to match against one value coming from the user.

This is a bad way to do it. Don't use comma-separated values; add a new table with a one-to-many relationship. Then the query will look like

<pre><code>SQL = "SELECT Product.* FROM Product, ProductCategory WHERE ProductCategory.ProductId=Product.ID AND ProductCategory.CategoryId = " &amp; Request.QueryString("CategoryId")
</code></pre>

If you really really want to do the comma-separated thing, you could do a search using LIKE and % wildcards, but it will be fragile (e.g. you'll have to make sure that an ID of "2" doesn't match "12").

blocks|key|1061988|text|这不是免费的，但是WHERE+ProductCategoryId+=+"+&+Request.QueryString("CategoryId")是一种非常糟糕的查询方式。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1061989|你刚刚对疯狂的SQL注入攻击敞开心扉。|1061990|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/SQL_injection^0|9|1R|0|7|5|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@]|D|@$9|T|A|U|1|V]]|E|$]]|$1|H|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

Not for nothing, but <code>WHERE ProductCategoryId = " &amp; Request.QueryString("CategoryId")</code> is a GOD AWFUL way of doing your query.

You've just opened yourself up to crazy <a href="http://en.wikipedia.org/wiki/SQL_injection" rel="nofollow noreferrer">SQL injection</a> attacks.

blocks|key|1062172|text|http://www.sommarskog.se/arrays-in-sql.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1062173|entityMap|0|LINK|mutability|MUTABLE|url^0|0|17|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@$A|L|B|M|1|N]]|C|$]]|$1|D|3|-4|5|6|7|O|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|4]]]]

<a href="http://www.sommarskog.se/arrays-in-sql.html" rel="nofollow noreferrer">http://www.sommarskog.se/arrays-in-sql.html</a>

blocks|key|2587353|text|回顾一下所有的注入问题，这是用于TSQL列表操作的最佳源。在不阅读整篇文章的情况下，下面是创建一个非常快速的自由循环的TSQL方法来拆分字符串所需的内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2587354|您将得到一个TSQL拆分函数，并且可以使用它如下：|2587355|SELECT
++++y.*
++++FROM+YourTable+y
++++++++INNER+JOIN+dbo.FN_ListToTable(',','1,2,3,444,5,,,6')+s+ON+y.ID=s.ListValue|code-block|syntax|javascript|2587356|从上一篇文章中，我更喜欢数字表方法。+--这就是实现它所需要做的事情。|2587357|要使此方法工作，您需要执行以下一个时间表设置：|2587358|SELECT+TOP+10000+IDENTITY(int,1,1)+AS+Number
++++INTO+Numbers
++++FROM+sys.objects+s1
++++CROSS+JOIN+sys.objects+s2
ALTER+TABLE+Numbers+ADD+CONSTRAINT+PK_Numbers+PRIMARY+KEY+CLUSTERED+(Number)|2587359|设置数字表后，创建以下函数：|2587360|CREATE+FUNCTION+[dbo].[FN_ListToTable]
(
+++++@SplitOn++char(1)++++++--REQUIRED,+the+character+to+split+the+@List+string+on
++++,@List+++++varchar(8000)--REQUIRED,+the+list+to+split+apart
)
RETURNS+TABLE
AS
RETURN+
(+++----------------
++++--SINGLE+QUERY--+--this+will+not+return+empty+rows
++++----------------
++++SELECT
++++++++ListValue
++++++++FROM+(SELECT
++++++++++++++++++LTRIM(RTRIM(SUBSTRING(List2,+number%2B1,+CHARINDEX(@SplitOn,+List2,+number%2B1)-number+-+1)))+AS+ListValue
++++++++++++++++++FROM+(
+++++++++++++++++++++++++++SELECT+@SplitOn+%2B+@List+%2B+@SplitOn+AS+List2
+++++++++++++++++++++++)+AS+dt
++++++++++++++++++++++INNER+JOIN+Numbers+n+ON+n.Number+<+LEN(dt.List2)
++++++++++++++++++WHERE+SUBSTRING(List2,+number,+1)+=+@SplitOn
+++++++++++++)+dt2
++++++++WHERE+ListValue+IS+NOT+NULL+AND+ListValue!=''
);
GO+|2587361|现在，您可以轻松地将CSV字符串拆分为一个表并在其上加入：|2587362|select+*+from+dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')|2587363|产出：|2587364|ListValue
-----------------------
1
2
3
4
5
6777

(6+row(s)+affected)|2587365|您可以将CSV字符串传递到过程中，并且只处理给定in的行，或者只在查询中使用它，如下所示：|2587366|SELECT
++++y.*
++++FROM+YourTable+y
++++++++INNER+JOIN+dbo.FN_ListToTable(',',@GivenCSV)+s+ON+y.ID=s.ListValue|2587367|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sommarskog.se/arrays-in-sql.html|1|http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum^0|P|3|0|0|0|0|0|I|1|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@$A|1H|B|1I|1|1J]]|C|$]]|$1|D|3|E|5|6|7|1K|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|1L|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|1M|8|@]|9|@$A|1N|B|1O|1|1P]]|C|$]]|$1|M|3|N|5|6|7|1Q|8|@]|9|@]|C|$]]|$1|O|3|P|5|H|7|1R|8|@]|9|@]|C|$I|J]]|$1|Q|3|R|5|6|7|1S|8|@]|9|@]|C|$]]|$1|S|3|T|5|H|7|1T|8|@]|9|@]|C|$I|J]]|$1|U|3|V|5|6|7|1U|8|@]|9|@]|C|$]]|$1|W|3|X|5|H|7|1V|8|@]|9|@]|C|$I|J]]|$1|Y|3|Z|5|6|7|1W|8|@]|9|@]|C|$]]|$1|10|3|11|5|H|7|1X|8|@]|9|@]|C|$I|J]]|$1|12|3|13|5|6|7|1Y|8|@]|9|@]|C|$]]|$1|14|3|15|5|H|7|1Z|8|@]|9|@]|C|$I|J]]|$1|16|3|-4|5|6|7|20|8|@]|9|@]|C|$]]]|17|$18|$5|19|1A|1B|C|$1C|1D]]|1E|$5|19|1A|1B|C|$1C|1F]]]]

Looking past all injection issues, this is the <a href="http://www.sommarskog.se/arrays-in-sql.html" rel="nofollow noreferrer">best source</a> for TSQL list manipulation. Without reading the entire article, here is what is needed to create a very fast loop free TSQL method to split strings.

You will end up with a TSQL split function, and can use it like:

<pre><code>SELECT
 y.*
 FROM YourTable y
 INNER JOIN dbo.FN_ListToTable(',','1,2,3,444,5,,,6') s ON y.ID=s.ListValue
</code></pre>

<a href="http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum" rel="nofollow noreferrer">from the previous article, I prefer the number table approach</a> and this is what you need to do to implement it.

For this method to work, you need to do this one time table setup:

<pre><code>SELECT TOP 10000 IDENTITY(int,1,1) AS Number
 INTO Numbers
 FROM sys.objects s1
 CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
</code></pre>

Once the Numbers table is set up, create this function:

<pre><code>CREATE FUNCTION [dbo].[FN_ListToTable]
(
 @SplitOn char(1) --REQUIRED, the character to split the @List string on
 ,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
( ----------------
 --SINGLE QUERY-- --this will not return empty rows
 ----------------
 SELECT
 ListValue
 FROM (SELECT
 LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
 FROM (
 SELECT @SplitOn + @List + @SplitOn AS List2
 ) AS dt
 INNER JOIN Numbers n ON n.Number &lt; LEN(dt.List2)
 WHERE SUBSTRING(List2, number, 1) = @SplitOn
 ) dt2
 WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO 
</code></pre>

You can now easily split a CSV string into a table and join on it:

<pre><code>select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')
</code></pre>

OUTPUT:

<pre><code>ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)
</code></pre>

Your can pass in a CSV string into a procedure and process only rows for the given IDs, or just use it in the query like:

<pre><code>SELECT
 y.*
 FROM YourTable y
 INNER JOIN dbo.FN_ListToTable(',',@GivenCSV) s ON y.ID=s.ListValue
</code></pre>

I've the following sql query:

<blockquote>
 SQL = "SELECT * FROM Product WHERE ProductCategoryId = " &amp; Request.QueryString("CategoryId")
</blockquote>

This query tells to get all the products from ONE category. I want the ability so products can be from some categories, and not from one category only.

So, I changed the [Product].ProductCategoryId field to varchar(50), and wrriten some categories id separated in commas, for example:

1,5,7,4

Now, how can I get this working in the SQL query? In ASP there are the Split and Contains (<a href="http://www.devx.com/vb2themax/Tip/18364" rel="nofollow noreferrer">http://www.devx.com/vb2themax/Tip/18364</a>) functions, but how can I do this in SQL?

Thanks, and sorry for English...

Classic ASP - Split and Contains in a simple SQL query

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我有以下sql查询：SQL = "SELECT * FROM Product =“& Request.QueryString("CategoryId")此查询告诉您从一个类别中获取所有产品。我想要的能力，使产品可以来自某些类别，而不是从一个类别。因此，我将Product.ProductCategoryId字段更改为varchar(50)，并用逗号分隔了一些类别id，例如：1,5,7,4现在，如何在

问一个简单的SQL查询中的典型ASP -拆分和包含
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问一个简单的SQL查询中的典型ASP -拆分和包含EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问一个简单的SQL查询中的典型ASP -拆分和包含
EN