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

Question

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

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.

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

First you need to tag SQL Server not MySQL. They are different. Second, you need to be more specific about how you need to query your table. There is nothing like ctrl + f in excel. — BICube, May 18 '15 at 06:42
`SELECT * WHERE column1 = 'left' OR column2 = 'left' OR ...` — moffeltje, May 18 '15 at 06:42
SQL is all about *structured* data - in which the (implied) type of data in each column is different. As such, there aren't many tools to assist with querying *across* columns (because such comparisons are usually meaningless). Your reference to Excel may be telling here - despite superficial similarities, tables in SQL are nothing like worksheets in Excel. If you find yourself struggling to do things that you could easily do in Excel, perhaps you've picked the wrong tool for the job? — Damien_The_Unbeliever, May 18 '15 at 07:53

shA.t · Answer 1 · 2018-06-30T03:49:42.357

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

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);

This fails for columns with DataType INT or Numeric – Parshuram Kalvikatte Jun 29 '18 at 11:57 — Parshuram Kalvikatte, Jun 29 '18 at 11:57

score 3 · Answer 2 · edited Mar 09 '20 at 14:57

3

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

It will provide you the row where Left is present

edited Mar 09 '20 at 14:57

Stevoisiak

23,794
27
122
225

answered Nov 14 '19 at 17:10

Aryan Sena

170
2
2
12

score 2 · Answer 3 · answered May 18 '15 at 06:44

2

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!

answered May 18 '15 at 06:44

Luca Nicoletti

102
11

Thanks for your reply , i find this query SELECT * FROM [Table Name] WHERE CONTAINS(*, 'value'); but its need to make creating full text catalog and full text search – Ahmed Jadalla May 18 '15 at 06:56

score 2 · Answer 4 · edited May 18 '15 at 06:53

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

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

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

Hopefully this will help.

score 1 · Answer 5 · edited May 23 '17 at 11:59

1

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

edited May 23 '17 at 11:59

Community

1
1

answered May 18 '15 at 08:00

tjeloep

318
1
4
19

score 0 · Answer 6 · answered May 18 '15 at 06:57

0

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

answered May 18 '15 at 06:57

Touseef Rehman

125
11

score 0 · Answer 7 · edited Mar 09 '20 at 14:58

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

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)

score -1 · Answer 8 · answered May 18 '15 at 06:48

-1

select * from students_all where left (
    student_name
    age
    student_id
    class

if all the columns are string type

answered May 18 '15 at 06:48

Indra Prakash Tiwari

947
1
6
15

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

8 Answers8