Use string contains function in oracle SQL query

sql

pattern-matching

contains

regexp

byNikita Barsukov·Aug 20, 2024

To identify if a string 'A' resides within another string 'B' in Oracle SQL, you can use INSTR(B, 'A') > 0 for an exact match or B LIKE '%A%' for pattern matching.

Here's how to use INSTR:

-- If you can find me in the crowd, raise your hands!
SELECT * FROM table_name WHERE INSTR(column_name, 'substring') > 0;

Or, the LIKE clause:

-- You like me? Coz I LIKE you too!
SELECT * FROM table_name WHERE column_name LIKE '%substring%';

Also, let's not ignore the case (pun intended) where searches need to be case-insensitive. Use the UPPER function:

-- I don't discriminate Upper or Lower case 
SELECT * FROM table_name WHERE UPPER(column_name) LIKE UPPER('%substring%');

Unleashing the wild(patterns) in search

When you decide to dive deeper into pattern matching, wildcards and the CHR() function can be your best buddies.

Let's start with LIKE mixed with wildcards:

-- All are welcome! Just bring along 'sub' in some form.
SELECT * FROM table_name WHERE column_name LIKE '%sub%';

-- Only front-ended 'sub' strings get the pass
SELECT * FROM table_name WHERE column_name LIKE 'sub%';

-- For the ones who like to 'sub' at the last minute
SELECT * FROM table_name WHERE column_name LIKE '%sub';

Did you say non-printable ASCII characters? Oracle has got CHR() for that:

-- If it leaks, it speaks! Matches newline in a string.
SELECT * FROM table_name WHERE column_name LIKE '%' || CHR(10) || '%';

Spreading wings with advanced pattern matching

Sometimes pattern-matching needs are not just simple 'A in B' scenarios. Regular Expressions with REGEXP_LIKE help in such intricate cases.

-- Got digits? You're in!
SELECT * FROM table_name WHERE REGEXP_LIKE(column_name, '\d');

-- Count'em all but leave the digit spills
SELECT COUNT(*) FROM table_name WHERE NOT REGEXP_LIKE(column_name, '^[0-9]+$');

-- Alpha lead, digit follow; Any problem?
SELECT * FROM table_name WHERE REGEXP_LIKE(column_name, '^[A-Za-z]\d*$');

Power to CONTAINS: Efficient searching in large data sets

While LIKE and INSTR are an easy choice and widely used, they may not be the most efficient in large datasets or columns. Allow CONTAINS with Oracle Text indexes to spearhead such scenarios.

-- Efficiency, you say? Oracle's CONTAINS is in the game with Text index.
SELECT * FROM table_name WHERE CONTAINS(column_name, 'search_term') > 0;

Remember, for specific ASCII characters, it's a step back to LIKE and INSTR as CONTAINS is the master of word and phrase searches.

Navigating multi-lingual data

In a multicultural world, our data can be multi-lingual too. Take the helm and steer your search to cover those cases:

-- ¡Hola! alistando códigos especiales.
SELECT * FROM table_name WHERE column_name LIKE '%a'||CHR(241)||'o%';

Don't forget to include NLS_UPPER function for case-insensitive matches in specific locales.

Performance considerations for the speed lovers

Optimize with indexing for CONTAINS: All good things come with a price. So be ready to invest in Oracle Text index.
Go easy with REGEXP_LIKE: They consume more, so keep these for complex pattern drills.
Poise with INSTR and SUBSTR: to fetch position information.
Watch out for full table scans: '%' prefix in patterns might get you there.