Count # of valus that exist in column string

I am trying to count # of rows where a value exists in any string of another table. Here is my example data:

So the totals would be:

I attempted to use different count by len functions, but was unable to make it work. I am hoping for help in this matter and getting an example of what to do.

Select ToolCode, Len(ToolCode), Count(WorkCenter)
FROM JT_ToolCode, JT_WorkCenter
WHERE ToolCode = Left(Workcenter,Len(ToolCode))
Group By ToolCode, WorkCenter

>Solution :

You can try something like this:

SELECT ToolCode.ToolCode, COUNT(*) AS Quantity
FROM ToolCode
JOIN WorkCenter ON WorkCenter.WorkCenter LIKE CONCAT('%', ToolCode.ToolCode, '%')
GROUP BY ToolCode.ToolCode;

This query looks at both the ToolCode and WorkCenter tables. It checks if each tool is mentioned in any work center. Then, it counts how many times each tool appears in the work centers and groups the results by tool.