Search All Fields 'Like' - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Search All Fields 'Like'

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-02 : 10:31:18

I've done some simple sql's for searching a field using Like,
But this one is different.

I am adding a param named @searchText

I would like to bring back all records in all the fields listed
below that has that string in the field...

WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND a.calLab = f.ID
AND a.testTechnology = g.id
AND (c.locationID = @location OR @location = 0)

So, each line/field above I want to search for the string and include
in the dataset.

Anyone can point me in the right direction?

And also, thanks everyone for their help.
I learn more sql everyday here and try to read more when I have time.

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-02 : 11:16:53

Now, I've tried something like:

WHERE (a.manufacturer = b.manufacturerID AND b.manufacturer LIKE @searchText)
OR (a.location = c.locationID AND c.location LIKE @searchText)
OR (a.Status = d.statusID AND d.status LIKE @searchText)
OR (a.EquipmentType = e.ID AND e.EquipmentType LIKE @searchText)
OR (a.calLab = f.ID AND f.labName LIKE @searchText)
OR (a.testTechnology = g.id AND g.testTechnology LIKE @searchText)
OR (c.locationID = @location OR @location = 0) --if @location = 0 then exclude

But it brings back soooooooooo many duplicate records

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-02 : 12:48:56

Ok, been working on this for a bit and it's working
but not bringing back all records.

At least it's not bringing back 10000 plus anymore.

Here's the full sql:

SELECT a.ID,
a.assignedID,
g.testTechnology,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
CAST(a.Description AS NVARCHAR) AS Description,
c.Location,
d.Status,
a.modifiedDate,
a.modifiedBy,
CAST(a.Notes AS NVARCHAR) AS Notes,
--a.Picture,
f.LabName,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber

FROM tblEquipments a,
tblManufacturers b,
tblLocation c,
tblStatus d,
tblEquipment_Type e,
tblLabs f,
tblTestTechnology g

--WHERE a.manufacturer = b.manufacturerID
WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND a.calLab = f.ID
AND a.testTechnology = g.id
AND (c.locationID = @location OR @location = 0 ) --if @location = 0 then exclude
AND(b.manufacturer LIKE @searchText
OR c.location LIKE @searchText
OR d.status LIKE @searchText
OR e.EquipmentType LIKE @searchText
OR f.labName LIKE @searchText
OR g.testTechnology LIKE @searchText
OR c.location LIKE @searchText
OR a.AssignedID LIKE @searchText
OR a.ModelNumber LIKE @searchText
OR a.SerialNumber LIKE @searchText
OR a.Description LIKE @searchText
OR a.ModifiedDate LIKE @searchText
OR a.Notes LIKE @searchText
OR a.calDate LIKE @searchText
OR a.calDueDate LIKE @searchText
OR a.AssetNumber LIKE @searchText)

Anyone see the problem?

Thanks,

Zath

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-02 : 13:29:38

Zath,

Just to verify, are you wrapping the text you are searching for with the wildcard character '%' ?

Just trying to rule out the easy stuff first.. :)

-Ryan

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-02 : 13:33:05

Ok, got it working!!!!

In query analyzer, I declared @location and set it to 1 and forgot

Set it to 0 to get all records and it worked!

Thanks for the help,

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources