Occurrence of a string inside another string

I was wondering if there is an easy way to look for a occurrence of a string inside another string. I’m using this query:

$nameId = "SELECT 
`testresults`.`id` AS TRid, requirementId, TestCaseId, TestSuiteCollectionId, TestSuiteId, TestSuiteCollectionName, `testcaserequirement`.`nameId` AS `Requirement`
FROM
testresultrequirementlink
    LEFT JOIN
`testresults` ON `testresultrequirementlink`.`testresultId` = `testresults`.`id`
LEFT JOIN
`testsuitecollection` ON `testresults`.`TestSuiteCollectionId` = `testsuitecollection`.`id`
LEFT JOIN
`testcaserequirement` ON `testresultrequirementlink`.`requirementId` = `testcaserequirement`.`id`
WHERE `TestSuiteCollectionName` LIKE '%".$checkfileName[0]."%'
GROUP BY `testresultrequirementlink`.`requirementId`;";

$checkfileName[0] will be set in a if-statement, where it’s set to GiM1_0. My problem is that the column doesn’t contain that specific string, only a part of it.

Image of sql table

So is there a way to “search / look” for GiM1_0 and get 'GiM10_Integrated_testbed1'. without removing the _? Maybe there is a way to see if some of the GiM1_0 is found? They both have GiM1.

Answer

You could remove underscores:

where replace(TestSuiteCollectionName, '_', ') LIKE concat('%', ?, '%')

The ? is a parameter placeholder. I encourage you to use parameters rather than munging query strings.

The above might match a few more things than you intend.

Alternatively, you could convert to a regular expression:

where TestSuiteCollectionName regexp replace(?, '_', '_?')

The question mark makes the _ optional. This is the narrowest answer to your question.