mysql - Populate column with number of substrings in another column -

i have 2 tables "a" , "b". table "a" has 2 columns "body" , "number." column "number" empty, purpose populate it.

table a: body / number

ababcdef / ijklmnop / qrstuvwkyz / 

table "b" has 1 column:

table b: values

ab cd qr 

here looking result:

ababcdef / 3 ijklmnop / 0 qrstuvwkyz / 1 

in other words, want create query looks up, each string in "body" column, how many times substrings in "values" column appear.

how advise me that?

here's finished query; explanation follow:

select   body,   sum(     case when value null 0          else (length(body) - length(replace(body, value, ''))) / length(value)          end   ) val (     select tablea.body, tableb.value     tablea     left join tableb on instr(tablea.body, tableb.value) > 0   ) charmatch group body 

there's sql fiddle here.

now explanation...

the inner query matches tablea strings tableb substrings:

select tablea.body, tableb.value tablea left join tableb on instr(tablea.body, tableb.value) > 0 

its results are:

body                 value -------------------- ----- ababcdef             ab ababcdef             cd ijklmnop qrstuvwkyz           qr 

if count these you'll value of 2 ababcdef string because looks existence of substrings , doesn't take consideration ab occurs twice.

mysql doesn't appear have occurs type function, count occurrences used workaround of comparing length of string length target string removed, divided length of target string. here's explanation:

• replace('ababcdef', 'ab', '') ==> 'cdef'
• length('ababcdef') ==> 8
• length('cdef') ==> 4

so length of string ab occurrences removed 8 - 4, or 4. divide 4 2 (length('ab')) number of ab occurrences: 2

string ijklmnop mess up. doesn't have of target values there's divide 0 risk. case inside sum protects against this.