Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: Use of LIKE operator

    Hi,

    I want to use the following SQL code.

    select *
    from test123
    where 'test' like name;

    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.

    Tried solutions:
    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 ?

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It looks like 10g is going to have regular expressions in SQL but it still wont get round the problem that your query has to go and check every row in test123 to see if it matches.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •