CODEWITHSUNDEEP
sqlsql-servert-sql
Mahantesh
Mahantesh

Reputation: 439

How does the LIKE operator work in SQL

I have an SQL query where I want to use like operator to select a column based on a string comparison using the LIKE operator.

The column value will have multiple machines associated with them, eg. M1,M2,M3,M15

If I use a single machine name in the LIKE operator, eg. 'where machineId like %M1%' will that return that column?

The same column should be returned if I send machine name as M2 or M3 or M15 in LIKE operator.

select 
    SC.ConsumptionNo[ReferenceNo], 
    Replace(convert(varchar, SC.ConsumptionDate,106), ' ', '-')[Consumed Date],
    sm.Spare_name[Spare Name],
    sm.SPart_No[Part Number],
    SC.Quantity[Consumed],
    um.UOM_Name[UOM],
    lm.Location_Name[Location Name],
    SC.UserId[User ID]
from Spare_Consumption SC
inner Join Spare_master sm on SC.SpareId = sm.Spare_Id
inner join Location_Master_Detail lm on SC.LocationId = lm.Location_Id
inner join UOM_Master um on sm.UOM = um.UOM_ID
where SC.ConsumptionDate >= Replace(CONVERT(varchar, cast(@StartDate as date), 106), ' ', '-')
and SC.MachineId like '%' + @SpareID + '%'
and SC.ConsumptionDate < Replace(CONVERT(varchar,  Dateadd(dd, 1, cast(@EndDate as date)), 106), ' ', '-')
and SC.LocationId = @LocationId

Upvotes: 1

Views: 103

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44971

',' + SC.MachineId + ',' like '%,' + @SpareID + ',%'

Upvotes: 1

Related Questions