How to find columns in mysql that starts with certain strings

Question

I have a table "myTable" with lots of columns with one column called "listing_id".

I want to find the listings that start with "CG_"

Also I will like to have count how many such are there?

You're using MySQL wrong. You **know** columns you need. You don't **ask** for columns, then select. Consider alternate permanent storage, something like Mongo or other types that don't have a schema. MySQL or RDBMS aren't for this usage scenario, unless you use EAV. — N.B., May 09 '14 at 11:56

score 1 · Accepted Answer · answered May 09 '14 at 11:58

1

Select count(listing_id)
from mytable
where listing_id like 'CG_%';

answered May 09 '14 at 11:58

G one

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

I would say you can use the information_schema as explained in here.

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `COLUMN_NAME` like 'CG_%';

edited May 23 '17 at 10:24

Community

answered May 09 '14 at 11:56

Majid

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

unclear what are you asking..Thos query select all string like cg_% under listing_id

I want to find the listings that start with "CG_"

Also I will like to have count how many such are there?column

SELECT count(listing_id)
FROM tablename
WHERE listing_id LIKE 'cg_%'

edited Jun 20 '20 at 09:12

Community

answered May 09 '14 at 11:58

Nagaraj S

3 Answers3