Published on February 2, 2024.

I'm often asked by my NetSuite clients to help them with interesting and somewhat unusual projects. For example, last month a client asked if there was a way to help their customer service team lookup a customer based on how their last name sounds. The goal was to make it easy for the a team member to pull up a customer when answering a call. They wanted the reps to be able to enter the name based on how the customer pronounced it.

My client was surprised when I told them that it's not only possible to do this, but also pretty easy to do. There's a Oracle function named "SOUNDEX" that "lets you compare words that are spelled differently, but sound alike in English." I won't go into the details of how the function works, but if you want to learn more about it, click here.

That said, SOUNDEX returns a string that represents the phonetic representation of a given string. For example, the value returned for my last name ("Dietrich") is D362. Similarly, it returns D362 for "Detrick" and "Detrich" and "Deitrich" and so on. So, if you want to see if two values sound the same, simply compare their SOUNDEX values.

Here's one of the queries that I developed for my client that uses SOUNDEX.

SELECT
	ID,
	LastName,
	FirstName,
FROM
	Customer	
WHERE
	( SOUNDEX( LastName ) = SOUNDEX( ? ) )
ORDER BY
	Lower( LastName ),
	Lower( FirstName )

In the WHERE clause, you can see that I'm comparing the SOUNDEX value of the customer record's LastName column to a value entered by the user. The user simply enters a value based on how the name sounds.

Here's an example of how the query was used in a prototype version of the app.

Click the image to view a larger version.

Here's the version of the query that was used.

SELECT
	'<a href="/app/common/entity/custjob.nl?id=' || Customer.ID || '" target="_' || Customer.ID || '">' || Customer.ID || '</a>' AS ID,
	Customer.LastName,
	Customer.FirstName,
	Customer.Email,
	EntityAddress.City,
	EntityAddress.State
FROM
	Customer	
	INNER JOIN EntityAddress ON 
		( EntityAddress.nkey = Customer.DefaultShippingAddress )
WHERE
	( SOUNDEX( Customer.LastName ) = SOUNDEX( ? ) )
ORDER BY
	Lower( Customer.LastName ),
	Lower( Customer.FirstName )

In that version of the query, I'm returning the ID as a link to the customer record. I'm also joining to the EntityAddress table to get the customer's default shipping address, so that I can show the city and state.

SOUNDEX is one of those functions that you probably won't need to use very often. But it's nice to know that it's there if you do need it.