Data Join with offset I have below situation:

Table1 

Field1  Field2
123     test1
234     test2


Table2

Field1  Field2
12345   test2
23245   test3
123945  test6


I want to join these two tables i.e. Table1 and Table2 where Field1 of table1 matches with first three characters of field1 of table2.

So results here should be:

Table1-field1 Table1-field2 Table2-field1 Table2-field2
123           Test1         12345         Test2
To do this, I did the following:

CREATED the two TABLEs

CREATE TABLE table1
(
  t1_field1 VARCHAR (20),
  t1_field2 VARCHAR (20)
);

CREATE TABLE table2
(
  t2_field1 VARCHAR (20),
  t2_field2 VARCHAR (20)
);


And populated it with data:

INSERT INTO table1 VALUES ('123', 'test1');
INSERT INTO table1 VALUES ('234', 'test2');

INSERT INTO table2 VALUES ('12345', 'test2');
INSERT INTO table2 VALUES ('23245', 'test3');
INSERT INTO table2 VALUES ('123945', 'test6');


And then ran the following SQL:

SELECT t1.t1_field1, t1.t1_field2, t2.t2_field1, t2.t2_field2 
FROM table1 t1, table2 t2
WHERE CAST(t1.t1_field1 AS VARCHAR(3)) = CAST(t2.t2_field1 AS VARCHAR(3));


Giving the result:

 t1_field1 | t1_field2 | t2_field1 | t2_field2 
-----------+-----------+-----------+-----------
       123 | test1     |     12345 | test2
       123 | test1     |    123945 | test6
(2 rows)


This SQL works if t1_field1 and t2_field1 are both VARCHARs or INTEGERs (and if one is VARCHAR and one is INTEGER).

This SQL works if they are both VARCHARs - 

SELECT t1.t1_field1, t1.t1_field2, t2.t2_field1, t2.t2_field2 
FROM table1 t1, table2 t2
WHERE LEFT(t1.t1_field1, 3) = LEFT(t2.t2_field1, 3);


I don't have S4 HANA to hand, but this is standard SQL and according to the documentation I Googled, it should work for Sybase which might be related?