user1170330 user1170330 - 6 months ago 24
MySQL Question

Remove suffix from column name

Some of my columns have the suffix

_a
at the end.

I want to check, if the column has such a suffix and if so, just remove it from my result set.

This is my query:

SELECT t1.column_name FROM information_schema.Columns t1
WHERE t1.table_name = 'myTab1' AND t1.table_schema = 'myDatabase1'


I need to add something like:

IF(SUBSTR(column_name, -2) == "_a", SUBSTR("column_name", 0, -2))


So that
col_a
becomes
col
.

I need to keep the suffix, so I can't just rename my column names completely.

vkp vkp
Answer

\ is to escape the special character _.

SELECT case when t1.column_name like '%\_a' then SUBSTRING(t1.column_name, 1, length(t1.column_name)-2)
            when t1.column_name like '%|a' then SUBSTRING(t1.column_name, 1, length(t1.column_name)-2)  
       else t1.column_name end
FROM information_schema.Columns t1
WHERE t1.table_name = 'myTab1' AND t1.table_schema = 'myDatabase1'

Based on the comment, if the string pattern being searched for should contain |a use t1.column_name like '%|a'

Sample fiddle