Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Match two words in a column

## I have two tables, and both have a column called full_name. I want to match at least two words in the full name column. For example, "John Alder Smith" and "John F Smith" is a match, while "Peter Duncan Doyle" and "Peter Parker" is not a match.

I have two tables, and both have a column called full_name. I want to match at least two words in the full name column. For example, "John Alder Smith" and "John F Smith" is a match, while "Peter Duncan Doyle" and "Peter Parker" is not a match. Thanks for your help.

The SQL part of the solution to this problem is to use a cross join, and then a WHERE clause to do the match:

```select t1.full_name as t1_name
, t2.full_name as t2_name
from table_one as t1
cross
join table_two as t2
where t1.full_name resembles t2.full_name ```

Why a cross join? Just to acknowledge that we are comparing every name in table one to every name in table two in order to find matches. At least, that's what the problem sounded like to me.

Now, about this mysterious resembles operator. Of course, there is no such thing, at least not tailor-made to match at least two words in two columns. Our challenge now is to find a way to do this with SQL.

The tough part is breaking up the name into words. For the general case, an unknown number of words, we might employ an auxiliary integers table, but let's assume that for full names, four words (names) is a good working maximum, as this simplifies the query a bit.

```select t1.full_name as t1_fullname
, t2.full_name as t2_fullname
from table_one as t1
cross
join table_two as t2
where case when ' '||t1.full_name||' '
like '% '||word(t2.full_name,1)||' %'
then 1 else 0 end
+ case when ' '||t1.full_name||' '
like '% '||word(t2.full_name,2)||' %'
then 1 else 0 end
+ case when ' '||t1.full_name||' '
like '% '||word(t2.full_name,3)||' %'
then 1 else 0 end
+ case when ' '||t1.full_name||' '
like '% '||word(t2.full_name,4)||' %'
then 1 else 0 end
>= 2```

Each CASE expression compares a separate word from t2.full_name, to the complete t1.full_name. Each LIKE comparison consists of two terms, with a space appended to both the front and back of each term being compared, like this:

`   ' Peter Duncan Doyle ' like '% Peter %'`

We need spaces around the name Peter in the right term, '% Peter %', because we don't want to match Peterson. We therefore also need to append a space to both the front and back of the left term, the entire full name, in order to find a word at the beginning or end of the full name.

Thus we test the first four words of t2.full_name, and for every word found within t1.full_name, we add 1 to a total. And if this total is 2 or more, the full names are considered to match.

The only thing we haven't done yet is explain how to extract the separate words out of t2.full_name. As you probably guessed, there is no WORD function. Depending on your database system, you might use some combination of nested POSITION and SUBSTRING functions, to extract words based on how many spaces you detect in the full name going from left to right. Granted, by the time you get to the CASE expression for the fourth word, with POSITION and SUBSTRING functions nested four deep, it does get ugly. For this reason, look to see if your database system offers any other string handling functions to make this part easier. For example, MySQL has the SUBSTRING_INDEX function, which can make this task easier. But if your database system allows you to declare a user defined function, then you could write your own WORD function and use it exactly as shown above.

#### Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### PostgreSQL 12 boosts open source database performance

Widely used open source PostgreSQL database platform gets a major update providing users with new SQL query capabilities for JSON...

• ### Data integration vs. ETL in the age of big data

Data integration provides a consistent view of business performance across multiple data sources, though it needs to keep up with...

• ### 5 steps to an improved data quality assurance plan

Consultant David Loshin offers tips on developing a data quality strategy that can help identify data errors before they cause ...

• ### ThoughtSpot BI platform an early adopter of AI

Due to AI, ThoughtSpot's analytics tools have been accessible to citizen data scientists from the start, Andrew Yeung, the ...

• ### ThoughtSpot 6 advances AI power of BI platform

New augmented intelligence and machine learning tools, along with a new mobile app, highlight the release of the new update to ...

• ### Promethium tool taps natural language processing for analytics

The software, called Data Navigation System, was designed to enable non-technical users to make complex SQL requests using plain ...

## SearchSAP

• ### CEO of SAP Bill McDermott abruptly resigns

Bill McDermott, CEO of longtime ERP, CRM and BI software vendor SAP, resigned Thursday triggering a succession plan that promotes...

• ### What is an SAP Intelligent Enterprise? Don't look for a clear answer

What does Intelligent Enterprise actually mean? If the SAP term confuses you, you're not alone. Learn what the experts say and ...

• ### Pros and cons of SAP migration to the cloud

Hosting SAP on cloud services can bring added security and scalability. But organizations must also watch out for issues such as ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Java video games, like Minecraft, keep the language popular

The next generation of Java developers can learn the ins and outs of the language through an unlikely source: video games such as...

• ### Pivotal, Microsoft team up to deliver Azure Spring Cloud

Azure Spring Cloud, jointly developed by Microsoft and Pivotal, lets Spring developers bring apps to the cloud without concern ...

• ### GitHub tempts enterprises with Semmle, security enhancements

With the Semmle semantic code analysis engine freshly added to its quiver, GitHub gives corporate development teams one way to ...

## SearchDataCenter

• ### Essential private cloud migration steps

Executive buy-in, application evaluation and tool configuration are all important parts of cloud selection. With these steps, ...

• ### IBM quantum computers' usefulness in sight -- using binoculars

IBM's Bob Sutor discusses Big Blue's new quantum systems and computation center, the realities of quantum computing today and how...

• ### Addendum sets ASHRAE 90.4 as energy-efficiency standard

The publication of ASHRAE 90.4 in 2016 brought a new set of energy guidelines, but the industry still used Standard 90.1. A new ...

## SearchContentManagement

• ### Digital asset management benefits the future of work

Digital asset management promises to transform digital work, making it easier to tag documents, analyze video and provide image ...

• ### Box cloud content management adds PDF tools, boosts security

Box partners with Adobe in releasing a new set of PDF tools that can be used within Box's content management cloud to enable ...

• ### AI optimizes content management workflows, personalizes CX

AI is poised to revolutionize content management workflows, bringing order to enterprise documents, greater insights into ...

## SearchHRSoftware

• ### Get smarter about HR data security

HR deals with mountains of sensitive employee data and must do a better job of protecting that information. Here's a look at how ...

• ### Walmart, FedEx make case for VR training

The payback from virtual training may be improved retention and shorter training times. An hour-long training period can be ...

• ### Do you know how to improve employee experience? Test yourself

Think you know what it takes to create a positive employee experience for your workers? Take this EX quiz to test your savvy and ...

Close