How to search every field of a table with MS SQL Server
Summary
The standard way of searching for ZZZ in the whole table is:
select column1, column2, ..., columnZ
from table
where column1 like '%ZZZ%' or
column2 like '%ZZZ%' or
...
columnZ like '%ZZZ%';
There are 2 problems with this approach:
- Difficult to maintain on wide tables.
- Slow on large tables.
Data used
select EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,JOB from emps; EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO JOB ------ ------------ ------- --------------- -------- ------- -------- 000010 CHRISTINE I HAAS A00 3978 PRES 000020 MICHAEL L THOMPSON B01 3476 MANAGER 000030 SALLY A KWAN C01 4738 MANAGER 000050 JOHN B GEYER E01 6789 MANAGER 000060 IRVING F STERN D11 6423 MANAGER 000070 EVA D PULASKI D21 7831 MANAGER 000090 EILEEN W HENDERSON E11 5498 MANAGER 000100 THEODORE Q SPENSER E21 0972 MANAGER 000110 VINCENZO G LUCCHESSI A00 3490 SALESREP 000120 SEAN O CONNELL A00 2167 CLERK 000130 DOLORES M QUINTANA C01 4578 ANALYST 000140 HEATHER A NICHOLLS C01 1793 ANALYST 000150 BRUCE ADAMSON D11 4510 DESIGNER 000160 ELIZABETH R PIANKA D11 3782 DESIGNER 000170 MASATOSHI J YOSHIMURA D11 2890 DESIGNER 000180 MARILYN S SCOUTTEN D11 1682 DESIGNER 000190 JAMES H WALKER D11 2986 DESIGNER 000200 DAVID BROWN D11 4501 DESIGNER 000210 WILLIAM T JONES D11 0942 DESIGNER 000220 JENNIFER K LUTZ D11 0672 DESIGNER 000230 JAMES J JEFFERSON D21 2094 CLERK 000240 SALVATORE M MARINO D21 3780 CLERK 000250 DANIEL S SMITH D21 0961 CLERK 000260 SYBIL P JOHNSON D21 8953 CLERK 000270 MARIA L PEREZ D21 9001 CLERK 000280 ETHEL R SCHNEIDER E11 8997 OPERATOR 000290 JOHN R PARKER E11 4502 OPERATOR 000300 PHILIP X SMITH E11 2095 OPERATOR 000310 MAUDE F SETRIGHT E11 3332 OPERATOR 000320 RAMLAL V MEHTA E21 9990 FIELDREP 000330 WING LEE E21 2103 FIELDREP 000340 JASON R GOUNOT E21 5698 FIELDREP (32 row(s) affected)
Concatenation
It's much simpler to search through only one string.
select FIRSTNME + '::' + LASTNAME + '::' + WORKDEPT + '::' + JOB as BIGSTRING from emps; BIGSTRING -------------------------------------------- CHRISTINE::HAAS::A00::PRES MICHAEL::THOMPSON::B01::MANAGER SALLY::KWAN::C01::MANAGER JOHN::GEYER::E01::MANAGER IRVING::STERN::D11::MANAGER EVA::PULASKI::D21::MANAGER EILEEN::HENDERSON::E11::MANAGER THEODORE::SPENSER::E21::MANAGER VINCENZO::LUCCHESSI::A00::SALESREP SEAN::O CONNELL::A00::CLERK DOLORES::QUINTANA::C01::ANALYST HEATHER::NICHOLLS::C01::ANALYST BRUCE::ADAMSON::D11::DESIGNER ELIZABETH::PIANKA::D11::DESIGNER MASATOSHI::YOSHIMURA::D11::DESIGNER MARILYN::SCOUTTEN::D11::DESIGNER JAMES::WALKER::D11::DESIGNER DAVID::BROWN::D11::DESIGNER WILLIAM::JONES::D11::DESIGNER JENNIFER::LUTZ::D11::DESIGNER JAMES::JEFFERSON::D21::CLERK SALVATORE::MARINO::D21::CLERK DANIEL::SMITH::D21::CLERK SYBIL::JOHNSON::D21::CLERK MARIA::PEREZ::D21::CLERK ETHEL::SCHNEIDER::E11::OPERATOR JOHN::PARKER::E11::OPERATOR PHILIP::SMITH::E11::OPERATOR MAUDE::SETRIGHT::E11::OPERATOR RAMLAL::MEHTA::E21::FIELDREP WING::LEE::E21::FIELDREP JASON::GOUNOT::E21::FIELDREP (32 row(s) affected)
Now it's quite simple to search through the BIGSTRING.
select empno, firstnme, lastname, workdept as dept, phoneno as phone, job from emps where firstnme + lastname + workdept + job like '%JE%'; empno firstnme lastname dept phone job ------ ------------ --------------- ---- ----- -------- 000220 JENNIFER LUTZ D11 0672 DESIGNER 000230 JAMES JEFFERSON D21 2094 CLERK (2 row(s) affected)
select empno, firstnme, lastname, workdept as dept, phoneno as phone, job from emps where firstnme + lastname + workdept + job like '%ON%'; empno firstnme lastname dept phone job ------ ------------ --------------- ---- ----- -------- 000020 MICHAEL THOMPSON B01 3476 MANAGER 000090 EILEEN HENDERSON E11 5498 MANAGER 000120 SEAN O CONNELL A00 2167 CLERK 000150 BRUCE ADAMSON D11 4510 DESIGNER 000210 WILLIAM JONES D11 0942 DESIGNER 000230 JAMES JEFFERSON D21 2094 CLERK 000260 SYBIL JOHNSON D21 8953 CLERK 000340 JASON GOUNOT E21 5698 FIELDREP (8 row(s) affected) </code
- % is the wildcard character in SQL.
- EN at the start followed by anything is: EN%
- EN at the end preceded by anything is: %EN
- EN anywhere in the middle is:
