Click here to Skip to main content
15,065,914 members
Please Sign up or sign in to vote.
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
Updated 11-May-20 23:08pm
v2

1 solution

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!
   
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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900