Join with patterns in the second table

Transact-SQL
1

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?

Thanks in advance.

2

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;
3

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

thank you very much

4

it works perfectly,

Thank you very much for your help