Using SQL to count occurrences of substring

[Mr Initial Man]

SQL Code so far:

SELECT charname "Character", Status, Description, count(comics.Characters) "Appearances"
FROM characters
JOIN comics ON Characters.CharName LIKE comics.Characters
GROUP BY charname;

Current Results:
If character.CharName = 'Sam', only instances where comics.Characters = 'Sam' are counted.

Needed Results:
If character.CharName = 'Sam', all instances where comics.Characters has the substring 'Sam' are counted. (i.e. if comics.Characters = 'Bud Sam' or comics.Characters = 'Jeff Sam', etc.)

Possible Issue
Would it be easier simply to create two seperate queries in the PHP page this statement will be used in: One to list character names, descriptions, and status; the other to count the number of appearances?

[mojtaba]

Hi,
Try using % sign before and after comics.Characters ... But remember it you must concatenation the string ('%') and Filed name ( comics.Characters)

Good luck,
Bye.

[Mr Initial Man]

This, I tried, with no luck. Here are the two versions I used:

Version 1:

SELECT charname "Character", status "Status", count(comics.Characters) "Appearances"
FROM characters
JOIN comics ON Characters.CharName LIKE concat("%",concat(comics.Characters,"%"))
GROUP BY charname;

Result:

+------------+-----------+-------------+
| Character  | Status    | Appearances |
+------------+-----------+-------------+
| Bud        | Regular   |          16 | 
| Dad        | Recurring |           1 | 
| Dorkboy    | Recurring |           5 | 
| Glen       | Recurring |           6 | 
| Jeff       | Regular   |          22 | 
| Johnny     | Recurring |           3 | 
| Nightlatch | Recurring |           6 | 
| Sam        | Regular   |          13 | 
+------------+-----------+-------------+
8 rows in set (0.02 sec)

Again, this simply returns how many "solo" appearances each has.

Version 2:

SELECT charname "Character", status "Status", count(comics.Characters) "Appearances"
FROM characters
JOIN comics ON Characters.CharName LIKE "%"||comics.Characters||"%"
GROUP BY charname;

+------------+-----------+-------------+
| Character  | Status    | Appearances |
+------------+-----------+-------------+
| Bud        | Regular   |         310 | 
| Dad        | Recurring |         310 | 
| Dorkboy    | Recurring |         310 | 
| Glen       | Recurring |         310 | 
| Jeff       | Regular   |         310 | 
| Johnny     | Recurring |         310 | 
| Nightlatch | Recurring |         310 | 
| Sam        | Regular   |         310 | 
+------------+-----------+-------------+
8 rows in set, 1 warning (0.02 sec)

This returns how many strips have appearances by any cast member, rather than the specific cast member.

[mattyblah]

what db are you using? sql server or mysql? something else?

[Mr Initial Man]

mysql