select column names that start with x

Question

Im trying to select all column names from a mysql table that start with pweb and then have an integer.

This works for all column names:

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME`='producten';

But this does not work unfortunately, how can I make this happen?

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME`='producten' AND `COLUMN_NAME` LIKE `pweb`;

Thanks!

By does not work do you mean the query doesn't fire at all or do you mean that it doesn't return any results? — TangoKilo, Aug 22 '13 at 09:52
`LIKE \`pweb\`` should be `LIKE 'pweb%'`. String literals aren't back ticked. — Martin Smith, Aug 22 '13 at 09:53
Your question needs to be clarified - do you mean pweb1, pweb2, pweb3 for example ? Or do you mean pweb and the column has a value of integer. You have 2 answers below for each. — SteveB, Aug 22 '13 at 09:59

score 11 · Accepted Answer · answered Aug 22 '13 at 09:58

11

Try this

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME`='producten' AND `COLUMN_NAME` LIKE 'pweb%'
AND DATA_TYPE = 'int'

answered Aug 22 '13 at 09:58

Padmanathan J

this did the trick, thank you, I did not know the % had to be added, LIKE has worked without for me in other instances. – Snuur Aug 22 '13 at 10:37

score 3 · Answer 2 · answered Aug 22 '13 at 09:56

you have to add the % after pweb.

so the sql statement select all column_name which begins with pweb**

Note: ** means other caracteres

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA, COLUMNS 
WHERE TABLE_NAME='producten' AND COLUMN_NAME LIKE 'pweb%';

Hope this help you

score 1 · Answer 3 · answered Aug 22 '13 at 09:56

1

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='producten' 
AND COLUMN_NAME LIKE 'pweb%'
AND DATA_TYPE = 'int'

answered Aug 22 '13 at 09:56

juergen d

3 Answers3