T-SQL and the WHERE LIKE %Parameter% clause
It should be:
...
WHERE LastName LIKE '%' + @LastName + '%';
Instead of:
...
WHERE LastName LIKE '%@LastName%'
Howto? Parameters and LIKE statement SQL
Your visual basic code would look something like this:
Dim cmd as New SqlCommand("SELECT * FROM compliance_corner WHERE (body LIKE '%' + @query + '%') OR (title LIKE '%' + @query + '%')")
cmd.Parameters.Add("@query", searchString)
Use of SqlParameter in SQL LIKE clause not working
What you want is:
tblCustomerInfo.Info LIKE '%' + @SEARCH + '%'
(or edit the parameter value to include the % in the first place).
Otherwise, you are either (first sample) searching for the literal "@SEARCH" (not the arg-value), or you are embedding some extra quotes into the query (second sample).
In some ways, it might be easier to have the TSQL just use LIKE @SEARCH
, and handle it at the caller:
command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%");
Either approach should work.
Using LIKE operator with stored procedure parameters
Your datatype for @location nchar(20)
should be @location nvarchar(20)
, since nChar has a fixed length (filled with Spaces).
If Location is nchar too you will have to convert it:
... Cast(Location as nVarchar(200)) like '%'+@location+'%' ...
To enable nullable parameters with and AND
condition just use IsNull or Coalesce for comparison, which is not needed in your example using OR
.
e.g. if you would like to compare for Location AND Date and Time.
@location nchar(20),
@time time,
@date date
as
select DonationsTruck.VechileId, Phone, Location, [Date], [Time]
from Vechile, DonationsTruck
where Vechile.VechileId = DonationsTruck.VechileId
and (((Location like '%'+IsNull(@location,Location)+'%')) and [Date]=IsNUll(@date,date) and [Time] = IsNull(@time,Time))
Case in where clause with column Like condition
try this:
select * from cms_tab
where
param_val is null
or col1 like '%'||param_val||'%'
Alter WHERE clause based on parameter
I would recommend boolean logic instead:
WHERE
(@active = 1 AND vp.date_installed IS NOT NULL AND vp.date_removed IS NULL)
OR (@active = 0 AND vp.date_removed IS NOT NULL)
Is there a combination of LIKE and IN in SQL?
There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.
Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:
Oracle:
WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0
SQL Server:
WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
The column you are querying must be full-text indexed.
Reference:
- Building Full-Text Search Applications with Oracle Text
- Understanding SQL Server Full-Text
Related Topics
Why Can't I Use Column Aliases in the Next Select Expression
SQL Keep Getting Error with on Update Cascade
Differencebetween Views and Materialized Views in Oracle
How to Create a Multi-Tenant Database with Shared Table Structures
Nested Select Statement in SQL Server
How to Compare Time in SQL Server
Sort Null Values to the End of a Table
Detect Overlapping Date Ranges from the Same Table
Given an Rgb Value What Would Be the Best Way to Find the Closest Match in the Database
How to Find Rows in One Table That Have No Corresponding Row in Another Table
How to Delete a Fixed Number of Rows with Sorting in Postgresql
What Are the Benefits of Using Database Cursor
Slow Simple Update Query on Postgresql Database with 3 Million Rows
Exists VS Join and Use of Exists Clause
Sqlite Alter Table Add Multiple Columns in a Single Statement
Normalization in Plain English
Window Functions: Last_Value(Order by ... Asc) Same as Last_Value(Order by ... Desc)