SQL : store the string function on a column and use it later in where clause-CodePudding

How can we store the value of a string function applied on a column value and use it later in where clause, ex:

select trim(lower(column)) as c from Table where c like '%pattern%' or c like '%pattern2%';

instead of repeating trim(lower(column)) in each where...like clause I want to compute it once and use it as above. The above statement throws an error about unknown column c

CodePudding user response：

This question has been asked many times, for example Using column alias in WHERE clause of MySQL query produces an error

I'll try to add value by describing all the alternatives I can think of.

You can repeat the expression in your WHERE clause:

select trim(lower(column)) as c from Table 
where trim(lower(column)) like '%pattern%' 
   or trim(lower(column)) like '%pattern2%';

You can define the alias in a derived table subquery, then reference the alias in the outer query:

select c from (
  select trim(lower(column)) as c from Table 
) as t
where c like '%pattern%' or c like '%pattern2%';

Similarly, you could use a CTE, but in this case it doesn't have any advantage over the derived table.

with cte(c) as (
  select trim(lower(column)) from Table 
)
select c from cte
where c like '%pattern%' or c like '%pattern2%';

You could use the HAVING clause. MySQL has a non-standard feature to support references to alias in the HAVING clause. I don't usually recommend this, because it may not work in other SQL brands, and I prefer to use HAVING only for its standard purpose of filtering groups, not filtering rows.

select trim(lower(column)) as c from Table 
having c like '%pattern%' 
   or c like '%pattern2%';

You could define a generated column for the expression you want, then use the generated column by name in your WHERE clause:

alter table Table add column c varchar(...) as (trim(lower(column)));

select c from Table
where c like '%pattern%' or c like '%pattern2%';

I will also comment that your example expression is not needed.

The use of LOWER() is probably not needed, because default collations are case-insensitive.

The TRIM() is also probably redundant, because the patterns in your example are bounded by wildcards.

So you can run the query you show simply:

select column from Table
where column like '%pattern%' or column like '%pattern2%';

You might ask "which solution has best performance?" (everyone asks about performance.) The answer is they all have bad performance in this example because the LIKE conditions have leading wildcards, and therefore the query will do a table-scan regardless.