SQL 如何搜索表中所有列中的特定值？

How to search about a specific value in all columns in the table?

提问by Ahmed Jadalla

I have a table (students_all) that contains 4 columns as below :-

我有一个students_all包含 4 列的表 ( )，如下所示：-

• student_name
• age
• student_id
• class

• 学生姓名
• 年龄
• 学生卡
• 班级

Now how can I search about a specific value such as 'left'in all columns in one query using SQL Server 2008.

现在如何'left'使用SQL Server 2008搜索特定值，例如在一个查询中的所有列中。

I mean how can make something like Excel (Ctrl+F) to find any value in all columns.

我的意思是如何让 Excel ( Ctrl+ F) 之类的东西在所有列中找到任何值。

回答by shA.t

There are many ways to check to find something like that in one SQL for a special table, So I suggest this way:

有很多方法可以检查在一个特殊表的 SQL 中找到类似的东西，所以我建议这样：

SELECT *
FROM students_all
WHERE student_name + age + student_id + class LIKE '%left%';

---

---

But for a dynamic way I use this:

但是对于动态方式我使用这个：

DECLARE @TableName nvarchar(256) = 'Table_1';
DECLARE @Find nvarchar(50) = '1';

DECLARE @sql nvarchar(max) = '';

SELECT @sql = @sql + 
    CASE 
        WHEN @sql = '' THEN ''
        ELSE '+ '
    END + 'CONVERT(nvarchar(max), ISNULL(' + cols.COLUMN_NAME + ', '''')) COLLATE DATABASE_DEFAULT '
FROM INFORMATION_SCHEMA.COLUMNS cols
WHERE cols.TABLE_NAME = @TableName;

SELECT @sql = 'SELECT * FROM ' + @TableName + ' WHERE ' + @sql + ' LIKE ''%' + @Find + '%''';

-- Or if you want to add other field types you can use this instead:
--SELECT @sql = 'SELECT * FROM ' + @TableName + ' WHERE CAST(' + @sql + ' AS NVARCHAR(MAX)) LIKE ''%' + @Find + '%''';

EXEC(@sql);

回答by Luca Nicoletti

SELECT *
FROM students_all as t
WHERE t.student_name == 'left'
      OR t.age == 'left'
      OR t.student_id == 'left'
      OR t.class == 'left'

There isn't a "fast" way to check every colums at one time, you need to check 1by1 every colum!

没有一种“快速”的方法可以一次检查每个列，您需要 1by1 地检查每个列！

回答by Abdullah

What i understand is that u want data which have value leftin any of column of table

我的理解是你想要left在表的任何列中 都有价值的数据

For that you can use

为此，您可以使用

SELECT *
FROM students_all
WHERE 
      student_name == 'left'
      OR age == 'left'
      OR student_id == 'left'
      OR class == 'left'

This will check give you the rows which have at least one column of value left

这将检查为您提供至少具有一列值的行 left

But i you want to get data which contain the word leftthe you need to change all those for conditions in WHEREto COLULMN_NAME LIKE %left%

但是我想获取包含单词left的数据，您需要将所有条件更改WHERE为COLULMN_NAME LIKE %left%

Hopefully this will help.

希望这会有所帮助。

回答by tjeloep

I think this earlier postis what the OP's trying to refer to. It requires modification, however, to search only from 1 specific table.

我认为这篇较早的帖子是 OP 试图引用的内容。但是，它需要修改才能仅从 1 个特定表中进行搜索。

回答by rosie chi

Modifying @shA.t's answerto allow for column names with spaces.

修改@shA.t 的答案以允许包含空格的列名。

DECLARE @TableName nvarchar(256) = 'TableName';
DECLARE @Find nvarchar(50) = 'SearchText';

DECLARE @sql nvarchar(max) = '';

SELECT @sql = @sql +
    CASE 
        WHEN @sql = '' THEN ''
        ELSE ' OR '
    END + '[' + cols.COLUMN_NAME +']' + ' LIKE ''%' + @Find +'%'''
FROM INFORMATION_SCHEMA.COLUMNS cols
WHERE cols.TABLE_NAME = @TableName;

select @sql = 'SELECT * FROM ' + @TableName + ' WHERE ' + @sql 
execute(@sql)

回答by Aryan Sena

SELECT *
FROM students_all
WHERE  'left' in (student_name , age ,student_id , class);

It will provide you the row where Left is present

它将为您提供 Left 所在的行

回答by Touseef Rehman

SELECT * FROM students_all as std
WHERE 
std.student_name = 'left'
OR std.age = 'left'
OR std.student_id = 'left'
OR std.class = 'left'

回答by Indra Prakash Tiwari

select * from students_all where left (
    student_name
    age
    student_id
    class

if all the columns are string type

如果所有列都是字符串类型