Look up for table with specific contraints (start with and end with)

[smallpotato]

Hi, I am looking for ways to look up for tables with below constraints

Table A (look up table)

Constraints to [code]	Start with	End with	ID
1	A	02	123
2	B	03	456
3	.........

Table B

Code	ID	column A	column B
A12302	000	xxx	xxx
A12301	000	xxx	xxx
B12303	000	xxx	xxx
B12304	000	xxx	xxx
C12303	000	xxx	xxx

If the [code] in table B, meet the constraints in table A, replace the [ID] in Table B with the corresponding [ID] in Table A.

For example, code [A12302] starts with "A" and end with "02", which matches constraint 1, then we have to replace its ID from "000" to 123

code "A12301" is starts with "A" but no end with "02", just keeps its ID as "000"

How should i implement this logic in alteryx?

[Qiu]

@smallpotato 

There is Function "StartWith" and "Endwith" in the Formula tool, maybe we can utilize them?

[CoG]

Alternatively, if your criteria are standardized (i.e. [Start with] is always a single character and [End with] is always two characters). Then you can pull out join criteria from [Code], and then Join on those criteria columns:

[nagakavyasri]

Another Alternative: