Prithviraj Mitra Prithviraj Mitra - 1 month ago 7
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

SELECT
wp_postmeta.post_id as ID, COUNT(*)
FROM
wp_postmeta
WHERE
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.

Answer

% mean anything, you should try with

SQL Fiddle Demo

SELECT *
FROM Table1
WHERE `meta_key` LIKE 'fl%sqft'
Comments