Prithviraj Mitra Prithviraj Mitra - 4 months ago 22
MySQL Question

Count maximum number of records using wildcard in mysql

I want to count how many records are there which starts with 'fl' and ends with 'sqft' in mysql database. The records are

post_id meta_key
1 fl_0_sqft
1 fl_1_sqft
1 _fl_0_sqft
1 _fl_1_sqft
2 fl_0_sqft
2 _fl_0_sqft
3 fl_0_sqft
3 fl_1_sqft
3 fl_2_sqft
3 _fl_0_sqft
3 _fl_1_sqft
3 _fl_2_sqft

I want the result set to be

ID Count
1 2
2 1
3 3

My query is

wp_postmeta.post_id as ID, COUNT(*)
wp_postmeta.meta_key LIKE '%fl'
GROUP BY wp_postmeta.post_id

The above query returns 0 record.

Is there anything like '%fl_*_sqft%' I can use after LIKE.

Any help is highly appreciated. Thanks in advance.


% mean anything, you should try with

SQL Fiddle Demo

FROM Table1
WHERE `meta_key` LIKE 'fl%sqft'