Search Varchar and Integer Columns in T-SQL

Question

I have a search functionality in my application which searches a string value of a particular column. The search text is passed as a parameter to the procedure. Now I have to search in another column which is an integer from another table. The input param is one but now I have to search in both the columns and return the result according to that.

//Query:

@SearchText VARCHAR(8000) = ''

SELECT DISTINCT Id, TransactionId, wfdFieldValue 
where (wfdFieldValue LIKE '%' + @SearchText + '%' OR @SearchText = '')

In the above query I have to include the TransactionId in the where condition. So that if the user searched using the TransactionId or the Fieldvalue the query will return the result.

Note: The TransactionId is an Integer DataType.

Answer 1

If I understand you correctly, you'll want a hit in either column to make the row show up in search results. In that case, you'll need a simple OR;

SELECT DISTINCT Id, TransactionId, wfdFieldValue 
FROM bop
WHERE @SearchText='' OR 
       wfdFieldValue LIKE '%' + @SearchText + '%' OR 
       TransactionId LIKE '%' + @SearchText + '%'

(You can just use LIKE on the integer column right away without manually converting.)

Of note though, this kind of wildcard search with a leading % does not use indexes in an optimal way, performance of this query will most likely not be great as the table grows.