SQL在表的所有列中搜索文本值 [英] SQL search all columns of a table for text value

问题描述

我有一个巨大的表，其中包含许多列，并且我知道此列有时会带有一些特定的值'MyValue'.如何在一个包含"MyValue"的特定表中选择所有行，而不管在哪一列中.

I have a huge table with many columns and I know that this columns sometimes takes some specific value 'MyValue'. How can I select all the rows in that one specific table containing 'MyValue' regardless in which column.

我在这里找到了相关主题: 如何在以下位置找到值一个SQL Server数据库?

I found related topic here: How do I find a value anywhere in a SQL Server Database?

但是我的查询需要一门较小的大炮.我知道表名，就是"MyTable".我不需要搜索整个数据库.

But my query needs a smaller cannon. I know the table name, it is, let's say 'MyTable'. I do not need to search the whole db.

推荐答案

要在简单的语句中执行此操作而无需特殊过程，可以将每一行转换为XML，然后对XML使用XQuery在其中搜索任何值.匹配的行.例如:

To do it without a special procedure in a simple statement, you could convert each row to XML and then use an XQuery on the XML to search for any value in the row that matches. So for example:

declare @SearchValue as varchar(20)
set @SearchValue = 'MyValue'

select *
    --,(select MyTable.* for XML PATH (''),TYPE) AllColumns
    --,convert(nvarchar(255),(select MyTable.* for XML PATH (''),TYPE).query('for $item in * where $item=sql:variable("@SearchValue") return $item')) FoundColumns
from MyTable
where convert(nvarchar(255),(select MyTable.* for XML PATH (''),TYPE).query('for $item in * where $item=sql:variable("@SearchValue") return $item'))<>''

专门为此任务设计的过程可能会更有效地执行此过程，并可以利用索引等优势.老实说，我不会在没有过多考虑的情况下将其放入生产数据库解决方案中，但是要综合考虑搜索工具，还不错.我在40秒内对700,000条记录表进行了搜索.但是，如果我按每列分别进行过滤，它将几乎立即运行.还有一些警告:

A procedure specifically designed for this task could probably do this more efficiently and could take advantage of indexes... etc. Honestly I would not put this into a production database solution without quite a bit of consideration, but as a throw together search tool it's not bad. I ran a search on a 700,000 record table in 40 seconds. However if I filter by each column individually it runs nearly instantly. Also a few more caveats:

• 所有表列都不能有空格或其他不友好的内容 XML标签的字符.我不知道如何获得带有空格的列名.也许有办法.
• 必须使用XQuery编写过滤器...这并不完全像 SQL.但是您可以使用=，<，>，甚至还有模式匹配.
• 查询函数的参数必须是字符串文字.所以 您不能动态地构建字符串.这就是为什么我将变量用作搜索值的原因，但是如果需要，也可以使用sql:column("ColName").
• 如果要搜索字符串以外的其他类型，则您使用的搜索字符串必须与将字段转换为XML值的条件完全匹配.

• None of the table columns can not have spaces or other unfriendly characters for an XML tag. I couldn't figure out how to get column names with spaces to work. Maybe there's a way.
• The filter has to be written in XQuery... which is not exactly like SQL. But you can use =, <, >, and there's even pattern matching.
• The parameter for the query function must be a string literal. So you can't build a string dynamically. This is why I used the variable for your search values, but you could also use a sql:column("ColName") if needed.
• If searching for other types besides strings, the search string you use must match exactly what the field would be converted to as an XML value.

这篇关于SQL在表的所有列中搜索文本值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！