CODEWITHSUNDEEP
sqlsql-server
Dan Goodwin
Dan Goodwin

Reputation: 57

Trouble joining tables with LIKE condition

Need help on a sql script looking between 2 tables but the join columns don't exactly batch.

Table1

FileName   Location

abc        C:\file

abc_TR     C:\fileTR

xyz        C:\file

Table2

FileName                TimeSentIn

abc_20230101            2023-01-01 15:35

abc_TR_20230101         2023-01-01 10:35

xyz_20230103            2023-01-01 15:30

rogert                  2023-01-01 15:31

footing_20230105-0545   2023-01-01 14:20

I need the output to be:

Table1.FileName    Table2.TimeSentIn

abc                2023-01-01 15:35

abc_TR             2023-01-01 10:35

xyz                2023-01-01 15:30

Is there any sql script I can use for this? I tried to use a LIKE condition in a join but that gave me duplicates for abc and abc_TR

Thanks,

Dan

Upvotes: 0

Views: 36

Answers (1)

gotqn
gotqn

Reputation: 43656

Try this:

DROP TABLE IF EXISTS file_location
DROP TABLE IF EXISTS file_sent


CREATE TABLE file_location (
  FileName VARCHAR(255) NOT NULL,
  Location VARCHAR(255) NOT NULL
);

INSERT INTO file_location (FileName, Location)
VALUES ('abc', 'C:\\file'), ('abc_TR', 'C:\\fileTR'), ('xyz', 'C:\\file');

CREATE TABLE file_sent (
  FileName VARCHAR(255) NOT NULL,
  TimeSentIn DATETIME NOT NULL
);

INSERT INTO file_sent (FileName, TimeSentIn)
VALUES ('abc_20230101', '2023-01-01 15:35:00'), ('abc_TR_20230101', '2023-01-01 10:35:00'), ('xyz_20230103', '2023-01-01 15:30:00'), ('rogert', '2023-01-01 15:31:00'), ('footing_20230105-0545', '2023-01-01 14:20:00');


SELECT file_location.FileName
     , file_location.Location
     , file_sent.TimeSentIn
FROM file_location
JOIN file_sent 
  ON file_location.FileName = REVERSE(SUBSTRING(REVERSE(file_sent.FileName), 10, LEN(file_sent.FileName)));

Upvotes: 1

Related Questions