Question About WildCard Usage in an SQL Statement

[JimStewart]

What is the Proper Way to use wildcards in an SQL query

Currently what I have is:

SELECT        ID, ProductID, Manufacturer, ProductMainCatagory, ProductSubCatagory, ProductSubSubCatagory, ProductName, PicturePath, ProductDesc, ProductRetailPrice, 
                         Deleted, DateModified
FROM            Products
WHERE        (ProductID LIKE '%'+@search+'%') OR
                         (Manufacturer LIKE '%'+@search+'%') OR
                         (ProductName LIKE '%'+@search+'%')

I have used th '&' symbol the '+' symbol and no symbol. I keep getting incompatiblilty warnings for use with TEXT columns

Thanks in advance

JimStewart

 

[jmcilhinney]

'text' and 'ntext' are deprecated data types. You should be using 'varchar' and 'nvarchar' instead. The code you have will work with those data types. You should change the data types if at all possible. If you can't for some reason, you will have to convert the values to one of those data types in your SQL code in order to use LIKE.

 

[JimStewart]

This is the error I get

"The data types varchar and text are incompatible with the add operator"

But I figured it out, I left the wildcards out of the SQL and added it to the text from the search box

Private Sub SearchToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchToolStripButton.Click
        Try
            Dim wildCardSearch As String = "%" & SearchToolStripTextBox.Text & "%"
            Me.ProductsTableAdapter.Search(Me.CatalogDataSet.Products, wildCardSearch)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

    End Sub

Thanks

 

[jmcilhinney]

Aha... so the wildcards do work with text and ntext then. From your symptoms and what I had read I thought not, but I guess it would make sense that they would. That said, you really should change the database to use varchar instead if you possibly can. Don't use any deprecated features if you don't need to.