SQL Or condition not working

When you are combining AND and OR conditions without braces, OR will bring all records out.
Kindly post the results of the query above and the expected result set to identify the issue..

@Raja Jegan R
see Results

typeName      typeName      stageName      typeID      stageID
Sourced               Sourced      Internal portal sourced        1563369937876

Expected Results : No results

Kindly double check for any blank spaces or special characters in the data by running the below query..
Below should return your result set expected and if not, then the StageName column contains some special characters.
Also confirm whether StageName column data is case sensitive or not..

  SELECT typeName,typeName,stageName,typeID,[stageID] FROM tblcandidate 
  WHERE candidateID='06f94c06364501'
  AND  [stageName] Like 'inter%'

Select allOpen in new window

  SELECT typeName,typeName,stageName,typeID,[stageID] FROM tblcandidate 
  WHERE candidateID='06f94c06364501'
  AND  [stageName] Like 'Inter%' -- To Check column case sensitiveness 

Select allOpen in new window

I think this is a case where Normalization shines :
You need a table that stores the different states

Sourced
Internal
Portal
........

Select allOpen in new window

You could then filter them without worrying for special chars or what soever

To avoid any case sensitive issue you could use the lower function:

SELECT typeName,typeName,stageName,typeID,[stageID] FROM tblcandidate 
  WHERE candidateID='06f94c06364501'
  AND ((lower(typeName) like 'hire%' OR lower(typeID) like 'hire%' OR lower(stageName) like 'hire%' OR lower([stageID]) like 'hire%')
    OR (lower(typeName) Like 'inter%' OR lower(typeID) like 'inter%' OR lower(stageName) like 'inter%' OR lower([stageID]) like 'inter%'))

Select allOpen in new window

Of course there are better ways to normalize your data, for starters I will create all these types under id, name description table and just use the id to identify your hire and inter data.

You are getting the proper results for your criteria:
For criteria:  
OR [stageName] Like 'inter%'
and the record has:
stageName
Internal
Why do you think this is incorrect?  You're looking for a record with [stageName] starting with "Inter" and "Internal" meets that criteria.

Think again and get back to us....

I will check and updated soon

Are you wanting your comparisons to be case-sensitive, where "inter" does NOT equal "Inter"?
This is called a "binary comparison".  Is that what you are wanting?