Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / SQL

How to do an Impossible Join with String Split

Rate me:
Please Sign up or sign in to vote.
3.00/5 (3 votes)
4 Mar 2022CPOL3 min read 4.6K   3   2
Use STRING_SPLIT() to join two tables
In this article, I’ll show you how to use STRING_SPLIT() to join two tables.

In this article, I’ll show you how to use STRING_SPLIT() to join two tables. I call this the impossible join.

I’ve come across this situation several times and always when dealing with data imported from two different sources. In most cases, it has to do with users inputting data inconsistently.

Let me show you.

Watch the Video

Don’t forget to check out the video. I go over the examples and provide more background for you to better understand it all!

The Impossible Join

In this example, I have two tables, Project and ProjectStatus. I would like to join ProjectStatus to Project to display the Name, ProjectStatus, and AccountNumber in the same result.

Image 1

If you look at ProjectStatus, ProjectName is a coder’s dream. The codes look great. The Project table, on the other hand, is a hot mess. The codes are buried within the column value.

There is no way to do a direct join from one to the other. In fact, only Project 9108 successfully joins.

Impossible Join Example

The Impossible Join Approach

The way we make the impossible join work is by splitting the ProjectName into pieces. This way, we don’t have to join on “F1432 plastic” but can look to join on the parts “F1432” or “plastic.”

As you can see from the example, “F1432” is part of the good ProjectName and will readily join between the two tables.

The main steps are:

  1. Split the hot mess column into pieces.
  2. Join into this separate piece to make a match.
  3. If a match is made, then join the two tables together.

So now that we have an idea how this is happening, let’s make it happen. To do so, we’ll first learn about STRING_SPLIT().

How STRING_SPLIT() Works

STRING_SPLIT() is used to break up a column into separate parts. It is a special type of function which returns a table as its result. It is similar to a Table Valued Function.

You can learn more about STRING_SPLIT() in this article I wrote, but the general idea is it takes the column you want to split, and the character, such as space that separates the values (aka words).

So STRING_SPLIT(‘F1432 plastic’, ‘ ‘) returns a table with two rows:

  • F1432
  • Plastic

In our example, let's look at what happens when you use string split in a query against Project:

Here is the tokenized table with account numbers:

SQL
select value ProjectName, AccountNumber
from Project
    cross apply string_split(ProjectName, ' ')

Image 3

There are a couple of things to notice here. First, since STRING_SPLIT() returns a table, we can use it in our query. You’ll see I’m using CROSS APPLY to join the STRING_SPLIT() result to the Project. I do so I can return a result showing each ProjectName value alongside the AccountNumber.

We now use this result in a final join to the ProjectStatus table to obtain our final result.

Using STRING_SPLIT() with INNER JOIN

At this point, that hard part is over. We have an intermediate result which includes the project “codes” as well as account numbers.

That last thing to do is relate this to the Project table.

I pull this all together in the following example:

SQL
Select s.ProjectName, s.ProjectStatus, p.AccountNumber
from ProjectStatus s
    left join (
        select value ProjectName, AccountNumber
        from Project cross apply string_split(ProjectName, ' ')
    ) p on s.ProjectName = p.ProjectName

Conclusion

IF you find yourself needed to join to a “code” embedded within another string, then you may find that STRING_SPLIT() allows you to get to the “code” to make the join successful.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
GeneralMy vote of 4 Pin
dmjm-h10-Mar-22 6:42
dmjm-h10-Mar-22 6:42 
I wish you had emphasized that the table is broken and should be fixed.
QuestionMessage Closed Pin
4-Mar-22 22:51
meysam shekarisaz4-Mar-22 22:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.