Sql Query For Like Operator in Stored Procedure

Question

How to Use Like operator in Left Outer Join Like select * from employee e LEFT OUTER JOIN City c On c.CityID-e.CityID Where LIke c.CityName '%Pune%'

Amit Kumar · Answer

Hi Use always single quote '%' in sql not double quote % dear.

Bharathi Raja · Answer

SELECT * FROM employee WHERE CityName LIKE '%Pune%' ;

Suraj Kumar · Answer

Hi Mohit You can do this as below Create Procedure DynamicSearch @SearchText varchar(50) AS BEGIN declare @SqlQuery nvarchar(2000) set @SqlQuery= 'select * from employee e LEFT OUTER JOIN City c On e.CityID = c.CityID where name LIKE '''+'%' +@SearchText+ '%' + '''' print @SqlQuery --If you want to see the output of query EXECUTE sp_executesql @SqlQuery END

Ankit Sharma · Answer

Hi Mohit Please try the below update query and let me know if it workd for you or not set @sqltext= ' Select HR.HotelID AS SearchID from HotelRegistration AS HR LEFT OUTER JOIN Category cat on cat.CategoryID=hr.CategoryID WHERE HR.IsDeleted 1 AND HR.HotelName LIKE '' % '+@SearchText+' % '' OR HR.[Address] LIKE '' % '+@SearchText+' % '' OR cat.CategoryName LIKE '' % '+@SearchText+' % '' ' exec (@sqltext)

Mohit Kala · Answer

but i am using this query in dynamic storedprocedure like this 'Select HR.HotelID AS SearchID from HotelRegistration AS HR LEFT OUTER JOIN Category cat on cat.CategoryID=hr.CategoryID WHERE HR.IsDeleted 1 AND HR.HotelName LIKE ''%''+@SearchText+''%'' OR HR.[Address] LIKE ''%''+@SearchText+''%'' OR cat.CategoryName LIKE ''%''+@SearchText+''%''

Mukesh Nayak · Answer

Hi We can write the query as following : SELECT * FROM Employee emp LEFT OUTER JOIN City city ON city.CityID = emp.CityID WHERE city.CityName LIKE '%Pune%' Regards Mukesh

Amit Kumar · Answer

Hi your query like operator come before field name. but it comes after field name. Remove like operator before c.CityName and use it after field name. exp: //use below Correct Query select * from employee e LEFT OUTER JOIN City c On c.CityID-e.CityID Where c.CityName LIke '%Pune%' Regards Amit