Check for a substring in a string in Oracle without LIKE

Question

How can I check for a substring in a string in Oracle without using LIKE  Let s say I want to select all users from a table that have the letter  z  in their last name   SELECT   FROM users WHERE last name LIKE   z      That would work  but I don t want to use LIKE  Is there some other function I could use

User · Answer

Bear in mind that it is only worth using anything other than a full table scan to find these values if the number of blocks that contain a row that matches the predicate is significantly smaller than the total number of blocks in the table. That is why Oracle will often decline the use of an index in order to full scan when you use LIKE '%x%' where x is a very small string. For example if the optimizer believes that using an index would still require single-block reads on (say) 20% of the table blocks then a full table scan is probably a better option than an index scan.

Sometimes you know that your predicate is much more selective than the optimizer can estimate. In such a case you can look into supplying an optimizer hint to perform an index fast full scan on the relevant column (particularly if the index is a much smaller segment than the table).

SELECT /*+ index_ffs(users (users.last_name)) */
       * 
FROM   users
WHERE  last_name LIKE "%z%"

User · Answer

Databases are heavily optimized for common usage scenarios  and LIKE is one of those    You won t find a faster way of doing your search if you want to stay on the DB-level

User · Answer

I m guessing the reason you re asking is performance   There s the instr function  But that s likely to work pretty much the same behind the scenes   Maybe you could look into full text search    As last resorts you d be looking at caching or precomputed columns an indexed view

User · Answer

If you were only interested in  z   you could create a function-based index   CREATE INDEX users z idx ON users  INSTR last name  z      Then your query would use WHERE INSTR last name  z    gt  0   With this approach you would have to create a separate index for each character you might want to search for   I suppose if this is something you do often  it might be worth creating one index for each letter   Also  keep in mind that if your data has the names capitalized in the standard way  e g    Zaxxon    then both your example and mine would not match names that begin with a Z   You can correct for this by including LOWER in the search expression   INSTR LOWER last name   z

User · Answer

You can do it this way using INSTR   SELECT   FROM users WHERE INSTR LOWER last name    z    gt  0    INSTR returns zero if the substring is not in the string   Out of interest  why don t you want to use like   Edit  I took the liberty of making the search case insensitive so you don t miss Bob Zebidee   -

[sql] Check for a substring in a string in Oracle without LIKE

Examples related to sql

Examples related to oracle