How to join two tables with common word in SQL server

Question

0.00/5 (No votes)

See more:

I have two tables with the following data:

              Table A                              Table B
ID | Text                        | Barnr.      Barnr. | Text
1  | Dan-salg 04.11              | 0             1001 | GOOGLE*GS
2  | Gebyr udland                | 0             1002 | Overforsel
3  | Buy EUR 10,00 GOOGLE*GS     | 0             1003 | Geby udlandet 
4  | Pbs overforsel FAKT         | 0             1004 | Dan-salg

I've tried using the following CODE but it doesn't seem to work for any row.

SQL

select OpgaveD.Id AS Bilag, OpgaveD.Dato, OpgaveD.Tekst, kd.Konto, OpgaveD.Modkonto, OpgaveD.Debet, OpgaveD.Kredit 
from OpgaveD left join KoKlDetail kd on kd.Navn LIKE CONCAT('%', SUBSTRING(OpgaveD.Tekst,1,(CHARINDEX(' ',OpgaveD.Tekst + ' ')-1)), '%') where OpgaveD.OpgaveId = 1

I get following:

ID | Text                        | Barnr.      
1  | Dan-salg 04.11              | 1004            
2  | Gebyr udland                | 1003             
3  | Buy EUR 10,00 GOOGLE*GS     | NULL             
4  | Pbs overforsel FAKT         | NULL

My question is: How can I see a better code that will read the common word in each table to return also 1001 and 1002 instead of NULL?

What I have tried:

SQL

select OpgaveD.Id AS Bilag, OpgaveD.Dato, OpgaveD.Tekst, kd.Konto, OpgaveD.Modkonto, OpgaveD.Debet, OpgaveD.Kredit 
from OpgaveD left join KoKlDetail kd on kd.Navn LIKE CONCAT('%', SUBSTRING(OpgaveD.Tekst,1,(CHARINDEX(' ',OpgaveD.Tekst + ' ')-1)), '%') where OpgaveD.OpgaveId = 1

Posted 11-May-20 22:18pm

Member 14085040

Updated 11-May-20 23:08pm

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2020-05-11T23:09:00

Solution 1

The simplest and the easiest way is to use PATINDEX[^] function. See:

SQL

DECLARE @tablea TABLE(ID int, [Text] VARCHAR(250))
DECLARE @tableb TABLE(ID int, [Text] VARCHAR(250))

INSERT INTO @tablea(ID, [Text])
VALUES(1, 'Dan-salg 04.11'),
(2, 'Gebyr udland'),
(3, 'Buy EUR 10,00 GOOGLE*GS'),
(4, 'Pbs overforsel FAKT')

INSERT INTO @tableb(ID, [Text])
VALUES(1001, 'GOOGLE*GS'),
(1002, 'Overforsel'),
(1003, 'Geby udlandet'),
(1004, 'Dan-salg')

SELECT ta.ID, ta.[Text], tb.ID ID2, tb.[Text] Text2
FROM @tablea ta LEFT JOIN @tableb tb ON PATINDEX('%' + tb.Text + '%', ta.Text) >0

Result:

ID	Text					ID2		Text2
1	Dan-salg 04.11			1004	Dan-salg
2	Gebyr udland			NULL	NULL
3	Buy EUR 10,00 GOOGLE*GS	1001	GOOGLE*GS
4	Pbs overforsel FAKT		1002	Overforsel

Good luck!

Posted 11-May-20 23:09pm

Maciej Los

Comments

CPallini 12-May-20 5:14am

5.

Maciej Los 12-May-20 5:17am

Thank you, Carlo.

MadMyche 12-May-20 8:15am

+5

Maciej Los 12-May-20 8:18am

Thank you.

Member 14085040 13-May-20 3:21am

Many thanks Maciej for your help. It seems super so far.
+5

Maciej Los 13-May-20 3:22am

You're very welcome.
Thank you.

Member 14085040 20-May-20 19:26pm

I also used the cod in another table, but I got the result:

DECLARE @tablea TABLE(ID int, [Text] VARCHAR(250))
DECLARE @tableb TABLE(ID int, [Text] VARCHAR(250))

INSERT INTO @tablea(ID, [Text])
VALUES(1, 'Gebyr, overf.'),
(2, 'Bgs Skat rykker'),
(3, 'Buy, EUR 10,00 GOOGLE*GS'),
(4, 'Pbs overforsel FAKT')

INSERT INTO @tableb(ID, [Text])
VALUES(1001, 'GOOGLE*GS'),
(1002, 'Overforsel'),
(1003, 'Ls, kreditor 20222 SKAT'),
(1004, 'gebyr udland')

SELECT ta.ID, ta.[Text], tb.ID ID2, tb.[Text] Text2
FROM @tablea ta LEFT JOIN @tableb tb ON PATINDEX('%' + tb.Text + '%', ta.Text) >0

Member 14085040 20-May-20 19:31pm

Result:
ID Text ID2 Text2
1 Gebyr overf. NULL NULL
2 Bgs Skat rykker NULL NULL
3 Buy, EUR 10,00 GOOGLE*GS 1001 GOOGLE*GS
4 Pbs overforsel FAKT 1002 Overforsel

Member 14085040 20-May-20 19:32pm

I'm missing here is that I need to use the common word (Gebyr and skat ) between table a and table b.
Is there any solution to that?

Maciej Los 21-May-20 2:07am

Yes, it's.
You need to use CTE to split sentece into words and then to find each word in another record.
This is less performant.

Member 14085040 25-May-20 10:40am

Thank you about it.