1

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.

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

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.

Community
  • 1
  • 1
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • Do you mean (1) all rows from the table, which has at least one row containing MyValue, or (2) just the rows containing MyValue? – jarlh Jan 13 '15 at 09:48
  • @jarlh I want to select all the rows containing 'john' no matter if 'john' is in FirstName column or LastName column or WhatEverOther coulmn. 'john' has to be in at least one column. – Przemyslaw Remin Jan 13 '15 at 10:22
  • I think this is bad DB design/idea. – i486 Jan 13 '15 at 10:36
  • Are you also using SQL Server or a different DBMS? –  Jan 13 '15 at 11:24

3 Answers3

7

You can do this by reversing the value and column in In operator.

SELECT *
FROM   Mytable
WHERE  'Myvalue' IN ( Col1, Col2, col3,.... )

If you don't want to type the columns, then pull it from information_schema.column view and create a dynamic query

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
4

You can concatenate all columns with + and then perform a LIKE search:

SELECT *
FROM   data
WHERE  col1 + '#' + col2 + '#' + col3 like '%test%'

Adding a separator (I use #) between the columns ensures you won't get false positives from the concatenation, e.g. if col2 = 'te' and col3 = 'st'

SQL Fiddle

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
3

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

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:

  • 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.
Brian Pressler
  • 6,653
  • 2
  • 19
  • 40