1

I have the following table on my SQL Server 2012:

ID Name Status Address Phone
1  Tom    I       U      D
2  Joe    D       U      D
3  Pam    D       I      U
4  Ken    U       U      U

How do I select the rows with 'I' in one of the columns? for example, I expect the query to return 1st and 3rd row from the table.

I know the query below works however I need a query that does not specify the column names as I need to deal with a table with more than 20 columns.

SELECT * FROM table WHERE (Status = 'I' or Address = 'I' or Phone = 'I')
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
sopint
  • 13
  • 2
  • Is there a way of using INFORMATION_SCHEMA.columns so I don't have to specify all column name explicitly? – sopint Jun 14 '18 at 06:10

2 Answers2

2

One way is to use XML:

SELECT t.*
FROM tab t
CROSS APPLY (SELECT * FROM tab t2 WHERE t.id = t2.id FOR XML RAW('a')) sub(c)
WHERE sub.c LIKE '%"I"%';

Output:

┌────┬──────┬────────┬─────────┬───────┐
│ ID │ Name │ Status │ Address │ Phone │
├────┼──────┼────────┼─────────┼───────┤
│  1 │ Tom  │ I      │ U       │ D     │
│  3 │ Pam  │ D      │ I       │ U     │
└────┴──────┴────────┴─────────┴───────┘

DBFiddle Demo

EDIT:

A bit more advanced option that excludes some columns. Basically simulating SELECT * EXCEPT id, name:

SELECT DISTINCT t.*
FROM tab t
CROSS APPLY (VALUES(CAST((SELECT t.* for XML RAW) AS xml))) B(XMLData)
CROSS APPLY (SELECT 1 c
             FROM B.XMLData.nodes('/row')  AS C1(n)
             CROSS APPLY C1.n.nodes('./@*') AS C2(a)
             WHERE a.value('local-name(.)','varchar(100)') NOT IN ('id','name')
               AND a.value('.','varchar(max)')  = 'I') C;

DBFiddle Demo2

Community
  • 1
  • 1
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • What if row with `ID=2` has `Address` field equal to `RIDGE RD. 32`? – Giorgos Betsos Jun 14 '18 at 06:12
  • @GiorgosBetsos Nothing. **[Demo](https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=2935f61dd62043eafd58dc4e17340c70)** – Lukasz Szozda Jun 14 '18 at 06:13
  • Ok, then something like `R. "I". RD. 32`? – Giorgos Betsos Jun 14 '18 at 06:15
  • @GiorgosBetsos Still nothing. What are you trying to accomplish? [Demo2](https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=1fcfa1c83958793f0097811292f18777) – Lukasz Szozda Jun 14 '18 at 06:16
  • I'm just trying to critically assess your answer. Asking questions is one way to do so. There are no bad intentions ... – Giorgos Betsos Jun 14 '18 at 06:18
  • @GiorgosBetsos Feel free to play with my demo - it is interactive :) Of course it will return row if name is `I` if you seek counterexamples(but I could make a version that simulate [`SELECT * EXCEPT name`](https://stackoverflow.com/questions/413819/select-except)). – Lukasz Szozda Jun 14 '18 at 06:18
  • 1
    Thanks @LukaszSzozda - this query really works for me! but I don't understand what sub(c) does there and couldn't find any relevant information from Google. Could you please elaborate a little bit? – sopint Jun 14 '18 at 07:13
  • sub(c) is just an alias for subquery and column. Subquery builds XML [demo](https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=b97b38aea161eb146e57f8f323471b89) – Lukasz Szozda Jun 14 '18 at 07:21
0

I don't know a way of doing your query without making any mention of column names. Here is one method which uses a string concatenation trick:

SELECT *
FROM yourTable
WHERE CONCAT(Status, Address, Phone) LIKE '%I%';

enter image description here

Demo

I don't think it is too difficult to list out 20 column names, once, in a query. If you need a way to get all columns from a table in SQL Server, then see here.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • *`"I need a query that does not specify the column names"`* – Lukasz Szozda Jun 14 '18 at 06:10
  • 1
    Thanks @Tim Biegeleisen - but my real table has dozens of columns so I definitely won't explicitly list all those columns in my query. – sopint Jun 14 '18 at 07:07
  • @sopint If you really have this many columns, then you should use dynamic SQL to build the query. Then, your current logic is just fine. If you can write out the columns, my answer is one option. – Tim Biegeleisen Jun 14 '18 at 07:14
  • There is one downside of this approach. If column can contain more than one character you won't be able to distinguish `'I'` from `'IA'` at least not without some kind of separator like `WHERE CONCAT(Status,'^', Address,'^' ,Phone) LIKE '%^I^%';` – Lukasz Szozda Jun 14 '18 at 07:23
  • @LukaszSzozda You're right, and I knew this when I posted, but the OP does seem to only have single letters. But this does the job of having to check only once for all columns, versus having to check every column. – Tim Biegeleisen Jun 14 '18 at 07:29