Query Search Across Multiple Columns

[tqeonline]

I have a Query that currently has a search box that is used as the parameter to query the results off of the "Part Number" column.

In the Gridview that shows the results there are Five columns:
Manufacturer, Location, Description, PartNumber, QTY

I would like to modify this query:

                <asp:SqlDataSource ID="dsSearch" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:SERVER-B InTables %>" 
                    SelectCommand="SELECT Stock_Mfg AS Manufacturer, Stock_Num AS Location, Stock_Desc AS Description, Stock_QOH AS QOH, GMPN + ' ' + DelphiPN + ' ' + FCIPN + ' ' + MolexPN + ' ' + MolexPN + ' ' + TycoPN + ' ' + YazakiPN + ' ' + EPCPN + ' ' + Other AS PN FROM Stock_Info WHERE (Stock_QOH > 0) AND (GMPN + ' ' + DelphiPN + ' ' + FCIPN + ' ' + MolexPN + ' ' + MolexPN + ' ' + TycoPN + ' ' + YazakiPN + ' ' + EPCPN + ' ' + Other LIKE '%' + ? + '%') UNION ALL SELECT Stock_Mfg AS Manufacturer, Stock_Num AS Location, Stock_Desc AS Description, Stock_QOH AS QOH, Stock_PN AS PN FROM ManEntry WHERE (Stock_QOH > 0) AND (Stock_PN LIKE '%' + ? + '%') ORDER BY Manufacturer, Location" 
                    ProviderName="<%$ ConnectionStrings:SERVER-B InTables.ProviderName %>" onselected="SqlDataSource1_Selected"
                    >
                    <SelectParameters>
                        <asp:ControlParameter ControlID="txtSearch" Name="?" PropertyName="Text" 
                            DefaultValue="%" />
                        <asp:ControlParameter ControlID="txtSearch" DefaultValue="%" Name="?" 
                            PropertyName="Text" />
                    </SelectParameters>
                </asp:SqlDataSource>

To be able to search the Manufacturer, Description, and PartNumber columns instead of just the PartNumber column.

I have tried a few things and cannot get it to recognize my query in different columns.

Right at the end of the query you see:

(Stock_PN LIKE '%' + ? + '%')

Is there a way to make it say "OR Stock_MFG LIKE '%' + ? + '%' OR Stock....." etc??

I know it is possible... any Ideas?

 

[markros]

Alternative way would be

charindex(?,Stock_MFG + ' ' + Stock_PN + ' ' + ) > 0

to search for the parameter in all these fields.



PluralSight Learning Library

 

[tqeonline]

I set it up sort of like you said

Stock_MFG + ' ' + Stock_PN + ' ' + Stock_Desc

However i think I need something a little different.

Say that Stock_MFG = "Delphi" and Stock_Desc = "Green Rubber Seal" and the search string is "Delphi Seal"

How could I get it to result that result?

 

[markros]

You want this record to be returned when you're searching for Delphi Seal?

Well, then it's a bit tricker. We need to first split our search string into a table
keywords(ID int, Word varchar(30))
1 Delphi
2 Seal

and then

select T.*, K.* from Table T inner join KeyWords K
on charindex(K.Word,T.Field1 + ' ' + T.Field2 + ' ' + T.Field3) > 0


PluralSight Learning Library