Published on January 17, 2024.

I recently worked on a custom mobile app for a NetSuite client. It included a function that sales reps can use to search for customers by keyword.

To implement the search function, I wrote a basic SuiteQL query. Here was the first version of it, which supports searching against company names.

SELECT
	ID,
	CompanyName,
	REPLACE( BUILTIN.DF( EntityStatus ), 'Customer-', '' ) AS Status,
	BUILTIN.DF( SalesRep ) AS SalesRep
FROM
	Customer
WHERE
	( CompanyName LIKE 'Carloni%' )
ORDER BY
	CompanyName

In the example, I'm searching for companies that start with the keyword "Carloni." Here were the results.

The query works, but only if the company name value is capitalized. For example, if the user enters "carloni" then the query fails.

One solution for this is to get the lower case column value by using the Oracle LOWER function.

SELECT
	ID,
	CompanyName,
	REPLACE( BUILTIN.DF( EntityStatus ), 'Customer-', '' ) AS Status,
	BUILTIN.DF( SalesRep ) AS SalesRep
FROM
	Customer
WHERE
	( LOWER( CompanyName ) LIKE 'carloni%' )
ORDER BY
	CompanyName

That query also works, and it returns the expected row. However, if the user were to capitalize the company name, then the query won't return the expected row. To resolve this, you'd need to use the LOWER function on both the column and the keywords, like this.

SELECT
	ID,
	CompanyName,
	REPLACE( BUILTIN.DF( EntityStatus ), 'Customer-', '' ) AS Status,
	BUILTIN.DF( SalesRep ) AS SalesRep
FROM
	Customer
WHERE
	( LOWER( CompanyName ) LIKE LOWER( 'Carloni%' ) )
ORDER BY
	CompanyName

That version of the query works and returns the expected row.

But there is a better solution - and one that I think more closely supports the type of keyword search behavior that users are expecting. It involves using Oracle's REGEXP_LIKE condition. REGEXP_LIKE is similar to the LIKE condition, but it uses regular expressions to evaluate the condition. This gives us the ability to perform case-sensitive and case-insensitive searches, as well as more advanced options such as supporting wildcards, dealing with multi-line values, and more.

Here's the final version of the query that I showed above.

SELECT
	ID,
	CompanyName,
	REPLACE( BUILTIN.DF( EntityStatus ), 'Customer-', '' ) AS Status,
	BUILTIN.DF( SalesRep ) AS SalesRep
FROM
	Customer
WHERE
	 ( REGEXP_LIKE( CompanyName, 'carloni', 'i' ) )
ORDER BY
	CompanyName

Notice that I've replaced the LIKE condition with REGEXP_LIKE. I'm indicating that I want all rows returned where the CompanyName is like "carloni" regardless of the how either value is capitalized. So the query will also work if the keyword was entered as "Carloni."

But the other nice thing about REGEXP_LIKE - and the fact that it is based on regular expressions - is that it also supports searching within the column value. In other words, the column doesn't have to start with the keyword.

For example, this query...

SELECT
	ID,
	CompanyName,
	REPLACE( BUILTIN.DF( EntityStatus ), 'Customer-', '' ) AS Status,
	BUILTIN.DF( SalesRep ) AS SalesRep
FROM
	Customer
WHERE
	 ( REGEXP_LIKE( CompanyName, 'build', 'i' ) )
ORDER BY
	CompanyName

... returns these 3 rows.

As I mentioned earlier, REGEXP_LIKE gives us the ability to provide our users with the type of keyword search functionality that they expect.

If you'd like to learn more about Oracle SQL supports's support for regular expressions, click here.

As always, I hope you've found this post to be helpful.