mysql, pattern matching

Ankita Kumari

4y829Learn iOS Programming

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar4y ago
Accepted 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 Singh4y ago
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 Ramamoorthi4y ago
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 Kumari4y ago
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.