Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Select Where Any Column Equals (or Like) Value Expand / Collapse
Author
Message
Posted Thursday, April 12, 2012 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 7:22 AM
Points: 1, Visits: 8
Great idea, but you either need to handle nulls ... isnull({{column}},'') or cast them as strings... Found this out the hard way.
Post #1282586
Posted Thursday, April 12, 2012 11:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
If you want to test equality conditions, you can do this:

Where @Parameter in (Col1, Col2, Col3)

That will find any column that is equal to the parameter value.

Can't do that so easily with Like instead of equality.

If it's a whole word, not a word fragment, you can find this kind of thing with full text indexing. Like "Fred" in "Fred and Wilma", not like "bam" in "Bambam".

Edit: And you'll definitely want to use IsNull or something similar if you concatenate columns together. That's been mentioned, but I thought I'd just reinforce that statement.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1282646
Posted Tuesday, April 17, 2012 4:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:53 AM
Points: 1,595, Visits: 6,607
I know this is an old thread, but just in case someone is still interested, here's a solution without explicitly referencing all columns and no dynamic SQL.

CREATE TABLE #Test
(
Name VARCHAR(100),
Age INT,
Description VARCHAR(100),
Field1 INT,
Field2 VARCHAR(100)
)
GO

INSERT INTO
#Test
VALUES
('Tom', 30, 'Something', 1, 'ghjk'),
('Jen', 23, 'Something', 2, 'fjfor'),
('Jim', 22, 'Something', 3, 'ghop'),
('Tom', 35, NULL, 4, 'asop')

DECLARE @SearchString VARCHAR(MAX)
SET @SearchString = '%gh%'

SELECT
*
FROM
#Test T
WHERE
EXISTS
(
SELECT
*
FROM
(
SELECT T.* FOR XML PATH, TYPE
) X(C) -- Turn all columns into XML
CROSS APPLY
X.C.nodes('//*/.') E(V) -- Get all XML elements
WHERE
E.V.value('./text()[1]', 'VARCHAR(MAX)') LIKE @SearchString -- Filter on value of the XML element
)

DROP TABLE #Test


Might be very slow on large tables because each row has to be composed into XML before filtering.

P.S. Tested on SQL 2008 R2, but I suppose this works on SQL 2005 as well
Post #1284808
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse