# Join with patterns in the second table

HI ,

I have to join two tables:

First table A , has a column tih these values AAAA , AAAB , AABB , AACC

Second table with two columns and these values : AAA* 100\$ , AA** 50€

I need to join two tables in order to obtain:

AAAA AAA* 100\$
AAAB AAA* 100€
AABB AA** 50€
AACC AA** 50€

These means i have to make a join with somekind of patterns included on the second table...

Is this possible?

Let's start with some consumable test data which you should have provided:

``````CREATE TABLE #TableA
(
YourColumnA varchar(10) NOT NULL
);
INSERT INTO #TableA
VALUES ('AAAA'),('AAAB'),('AABB'),('AACC');

CREATE TABLE #TableB
(
YourColumnB varchar(10) NOT NULL
,YourValue varchar(10) NOT NULL
);
INSERT INTO #TableB
VALUES ('AAA*', '100\$'), ('AA**', '50€');``````

The following works:

``````WITH JoinOrders
AS
(
SELECT 4 - LEN(REPLACE(YourColumnB, '*', '')) As JoinOrder
,REPLACE(YourColumnB, '*', '[A-Z]') AS LikeColumn
,YourColumnB
,YourValue
FROM #TableB
)
,AllJoins
AS
(
SELECT A.YourColumnA, J.YourColumnB, J.YourValue
,ROW_NUMBER() OVER (PARTITION BY A.YourColumnA ORDER BY J.JoinOrder) AS rn
FROM #TableA A
JOIN JoinOrders J
ON A.YourColumnA LIKE J.LikeColumn
)
SELECT YourColumnA, YourColumnB, YourValue
FROM AllJoins
WHERE rn = 1
ORDER BY YourColumnA;``````

awesome , let my try it... and i will tell you

thank you very much

it works perfectly,

Thank you very much for your help