flwr_pwr flwr_pwr - 1 year ago 80
SQL Question

ORACLE Join tables on a single field and account for minor difference (specific example provided)

I am trying to join two tables on a field (FILE_NAME); however, there are a couple records in just one of the table, in which a timestamp is appended to the end of the file name and before the file extension. I'm not sure how to account for these. I found an Oracle function,
REGEXP SUBSTR (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions131.htm), that seems like it may give me what I need, but I have to admit that this is extremely advanced to me and am not sure how to apply it.

My sample tables are:

FILE_INFO Table:

FILE_NAME | FILE_ID
REGIONS_ACCOUNTED.xlsx | 21
TSM_INSAT.xml | 14


FILE_PARAMETERS Table:

FILE_NAME
TSM_INSAT.xml
REGIONS_ACCOUNTED-08112017.xlsx


From what I can tell, it seems that the timestamps are always prefixed with a dash (-) so I originally thought to approach it by finding the index of a dash then use substr to concat the before and afters of the timestamp but can't figure out how to do that in a query or how to account for date ranges (e.g.:

REGIONS_ACCOUNTED-07102017-07142017.xlsx


At this point, I just have a basic Join:

SELECT a.file_name, b.file_location
FROM reports.file_info a
LEFT OUTER JOIN reports.file_parameters b on (a.file_name = b.file_name);


The SQL above of course excludes those reports with dates/date ranges in the filename. It would be better to use a file_id, I'm sure; however, there is no file_id in the file_parameters.
Any guidance would be greatly appreciated!

Answer Source

@Hepc provided the correct answer (in the comments). The modified version to account for date ranges is:

REGEXP_REPLACE(a.file_name,'\-[\d\w\-\_]+.,'.') 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download