It is unclear whether the method in your example is right or best for you, but if you need to query or return the same expression in many different queries, you can add a custom column to the table itself. In MySQL, this is called a generated column:
custom_column VARCHAR(50) AS
This way the column will be available for reference in any part of any query on
custom_column = 'xyz'
A live demo of this approach is available at db<>fiddle.uk.
On the other hand, if this is a one-off occasion where you simply need to be able to reference a calculated column in the
WHERE clause of one specific query, then since MySQL 8 you can define such a column using a
LATERAL join to a
SELECT. Once you have defined the column in this way, you can reference it by name in any part of the query, including the
MY_TABLE AS t
CROSS JOIN LATERAL
SUBSTRING(column2, 4) AS custom_column
) AS c
c.custom_column = 'xyz'
You can play with this method in another live demo.