MySQL query column name substring

Question

Is it possible to combine the use of back ticks ` with the substr() function? I have column names with hyphens '-' which require the use of backticks to query but seems like I cannot do something like:

  SELECT substr(`abc-def-ghi`,1,5) FROM tableName

Where the aim is to select all rows from columns starting with abc-d. Unfortunately changing/removing the hyphens is not an option.

Gordon Linoff · Accepted Answer · 2016-06-06 10:55:51Z

2

You cannot do what you want with a simple SQL statement. When you use abc-def-ghi, you get the value of the column, not the name.

A SQL SELECT statement returns a fixed set of columns with a fixed set of names, You cannot select names that way.

If you want to do something like this, then you would need to use dynamic SQL (prepare and exec). However, I suspect that you might have a poor data model. You might want to ask another question, show the table layout, and ask if that is a reasonable data structure for what you are trying to do.

answered Jun 6, 2016 at 10:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dter Over a year ago

Thanks for your answer Gordon. I guess I have to make "changing column names" an option after all. My model is indeed not optimal since I'm aware it would be better to query the rows than the columns, however I'm constrained by the limit on the number of columns; hence had to set the higher dimension as the rows (higher limit) and the other dimension as columns.

Collectives™ on Stack Overflow

MySQL query column name substring

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related