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?

Answer 1

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.

Answer 2

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.

Answer 3

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') > 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').

Answer 4

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%"

Answer 5

You can do it this way using INSTR:

SELECT * FROM users WHERE INSTR(LOWER(last_name), 'z') > 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. :-)