Join tables with like method (contains)

[#1]

hey, is it possible to join tables not with equal sign (=) but with like method (%) or something similar?
for example: in one table I have column of names, in the second table I have column of parts of those name and i want them to match
"america"--> "ame", "eric" ect...

[#2]

One way that could work to do this, would be to create a new column in the matching data table, and to use the contains within the formula in that column.  If the target column contains "ame" and "eric" then the new column would be given the value "america".  Then the joining would be completed by matching with the new formula column.

[#3]

You could also use Query (but I would most likely formula + update/join method):

Names Default To Here(1);

dt1 = New Table("Untitled",
	Add Rows(4),
	New Column("Col", Character, "Nominal", Set Values({"america", "america", "test", "test"})),
	New Column("Val", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, 4]))
);

dt2 = New Table("Untitled",
	Add Rows(2),
	New Column("Col", Character, "Nominal", Set Values({"ame", "rica"})),
	New Column("Val", Numeric, "Continuous", Format("Best", 12), Set Values([11, 22]))
);

wait(1);
dt3 = Query(
	Table(dt1, "t1"),
	Table(dt2, "t2"), 
	"\[SELECT *
	FROM t1
	LEFT OUTER JOIN t2 ON t1.col like '%'||t2.col||'%'
	]\"
);

[#4]

thank you for your answer,

how do I install SQL in JMP?

I keep getting this error:

[#5]

I think that should work by default in JMP without SQL installations. Which JMP version are you using?

SQL Functions (jmp.com) , Write a SQL Query (jmp.com)

[#6]

jmp12

[#7]

It could be that JMP doesn't offer that function, but I'm not sure about that.

[#8]

The SQL Query functionality was added in JMP 13

The following syntax failed in JMP 12 but executed without error in JMP 13

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
result = Query( dt, "SELECT * FROM 'Big Class' WHERE age > 14;" );

[#9]

thank you