mysql, pattern matching

Question

write a sql query for searching using pattern matching. my query is SELECT * FROM tbl_candidate WHERE `job_title` LIKE '%%' OR `candidate_city` LIKE '%glassboro%'. it gives wrong output

Jignesh Kumar · Answer

Hi Ankita, You can refer below link which will help you out, declare @job_title varchar(100) declare @candidate_city varchar(100) set @candidate_city = 'glassboro' SELECT * FROM tbl_candidate WHERE (@job_title IS NULL OR job_title LIKE '%' + @job_title + '%') AND (@candidate_city IS NULL OR candidate_city LIKE '%' + @candidate_city + '%') https://sqlwithmanoj.com/2011/12/30/creating-stored-procedures-with-dynamic-search-filter/

Sachin Singh · Answer

test this CREATE PROCEDURE sp_YourSPName /* Input Parameters */ @JobTitle varchar(250), @City varchar(250) AS egin SET NOCOUNT ON /* Variable Declaration */ DECLARE @SQLQuery AS NVARCHAR(4000) DECLARE @ParamDefinition AS NVARCHAR(2000) /* Build the Transact-SQL String with the input parameters */ SET @SQLQuery = 'Select * From tbl_candidate where (1=1) ' /* check for the condition and build the WHERE clause accordingly */ IF (@JobTitle IS NOT NULL) AND (@City IS NOT NULL) SET @SQLQuery = @SQLQuery + ' And (job_Title like ' % '+@JobTitle+ ' % ' AND city like ' % '+@City+' % ')' IF (@JobTitle IS NULL) AND (@City IS NOT NULL) SET @SQLQuery = @SQLQuery + ' And (city like ' % '+@JobTitle+' % ')' IF (@JobTitle IS NOT NULL) AND (@City IS NULL) SET @SQLQuery = @SQLQuery + ' And (job_Title like ' % '+@JobTitle ' % ' )' end

Srinivasan Ramamoorthi · Answer

Based on your search condition add wild card characters to search string. If you want to search text containing then go for %text%. Starts with use text% Ends with use %text

Ankita Kumari · Answer

then how we write combine query for searching like1. search on the basis ofjob_title 2. seach on the basiss of job_title or city 3. search on the basis of job_title and city. these are the search criteria but i want if we pass job_title then it will result only job_title using patternmatching, if we want to pass job title or city any one of them then it wll show only the passed matched string.