You can try something like this (requires only a single table scan):

SELECT

@abc = COUNT(CASE WHEN ColumnName LIKE '%abc%' THEN 1 END),

@def = COUNT(CASE WHEN ColumnName LIKE '%def%' THEN 1 END),

@Ghi = COUNT(CASE WHEN ColumnName LIKE '%ghi%' THEN 1 END),

@jkl = COUNT(CASE WHEN ColumnName LIKE '%jkl%' THEN 1 END),

@mno = COUNT(CASE WHEN ColumnName LIKE '%mno%' THEN 1 END),

@pqr = COUNT(CASE WHEN ColumnName LIKE '%pqr%' THEN 1 END),

@stu = COUNT(CASE WHEN ColumnName LIKE '%stu%' THEN 1 END),

@vw = COUNT(CASE WHEN ColumnName LIKE '%vw%' THEN 1 END),

@xzy = COUNT(CASE WHEN ColumnName LIKE '%xyz%' THEN 1 END)

FROM

TableName