Header and footer filter
I have this request
select name || ' ' || sure_name from users
which has this result
'test test'
'test1 test1'
...
I need to create a filter for this request. But I'm wondering what the best way to create it would be. I came up with this:
select name || ' ' || sure_name from users
where name || ' ' || sure_name = 'test test'
But I'm wondering how efficient this query would be, since the concatenation happens twice (in the select statement as well as in the where statement)
EDIT
the filter might look like
like '%test t'
source to share
Concatenation itself is not a problem, but string access.
For example, if you have a pointer to sure_name or by name, it is better to query with separate columns.
select name || ' ' || sure_name from users
where name ='test' and sure_name = 'test';
But if you don't have indices, don't worry. The performance will be about the same as your request.
However, if you add an index
create index fbi_full_name on users(name || ' ' || sure_name)
your query will perform better.
source to share
Check each field separately so that if you have indexes on name
or sure_name
, these indexes can be used more efficiently:
SELECT name || ' ' || sure_name
FROM users
WHERE name = 'test'
AND sure_name = 'test'
Note that if the name or username can contain spaces, then the two queries may give different results.
source to share
In such a case, I prefer to express it with a subquery:
select *
from (select (name || ' ' || sure_name) as newname
from users
) t
where newname = 'test test'
This way my logic is a where
little more isolated from the variable creation logic. It's easier for me to read and maintain.
source to share