Note that, like is used not in the normal way. Because, the column name stores paterns like '%' 'te%' '_e%'. So, all those matching rows should be in the result set. However, this slow down the performance. So, please help me to improve the performance while having the same result set.
use of = with OR. However, patterns stored in the column name may be infinite. So, it is not possible to specify all the possibilities.
And if I just use the =, result set contains only one column with name = 'test' ( as it should be).
Is there a way that I can create an Index ( functional index !!) to speed the this ?
Well like is an operator so you cant create a function based index and even if you wrote your own function like() it still wouldnt work cos % and _ are not values (they can match any number of values) so they wouldnt work in an index btree anyway.
What is the purpose of this code, do you really need to do this? You might be better off say using some other technology like java and using its regular expression libraries.