How to ignore trailing slash while joining two string columns in SQL

Question

I have two tables tableA and tableB as described bellow

desc tableA
GUID
PROPERTY_NAME
PROPERY_VALUE
ANOTHER_COL_1
ANTHER_COL_2

desc tableB
GUID
PROPERY_NAME
PROPERTY_VALUE
RANDOM_COL_1
RANDOME_COL_2

I have the following query to fetch the list of entries which have matching values for property_name which here is "URL"

SELECT A.GUID as SOURCE_GUID, 
       B.GUID as DESTINATION_GUID 
FROM
       tableA A,
       tableB B
WHERE
       A.PROPERTY_NAME = "URL" AND
       A.PROPERY_NAME  = B.PROPERTY_NAME AND
       A.PROPERTY_VALUE = B.PROPERTY_VALUE

The issue here is as propery values are URLs, they may or may not have trailing slash. I want the join to happen regardless of trailing slash. Please suggest a better way of achieving the same with out using an PL/SQL procedures to remove the trailing slash.

"Please suggest a better way of achieving the same with out using an PL/SQL procedures to remove the trailing slash" Translation: *I have poor data quality processes but I want to keep them because I like creating work for myself.* — APC, Nov 29 '11 at 10:48

score 7 · Answer 1 · edited Nov 29 '11 at 20:10

You have two options.

The first is clean your data before use.

Before any URL is stored in the system, add or remove (as prefered) a slash if necessary. In this way ensure that all URLs are stored in the same format for ease of use.

The second is clean your data at run time.

I do not advocate these options as they incur unnecessary overheads and can prevent optimal use of indexes.

RTRIM(A.PROPERTY_VALUE, '/') = RTRIM(B.PROPERTY_VALUE, '/')

Cleanest code, but likely prevent index use.

Values from both Table A and Table B are being modified
This likely requires a SCAN on both tables

Or...

(Ensure B.PROPERTY_VALUE ends with '/', then do the comparison); OR
(Ensure B.PROPERTY_VALUE does NOT end with '/', then do the comparison)

If either of these conditions are true, the URLs match.

A.PROPERTY_VALUE = (CASE WHEN RIGHT(B.PROPERTY_VALUE, 1) = '/' THEN B.PROPERTY_VALUE ELSE B.PROPERTY_VALUE + '/' END)
OR
A.PROPERTY_VALUE = (CASE WHEN RIGHT(B.PROPERTY_VALUE, 1) = '/' THEN RTRIM(B.PROPERTY_VALUE, '/') ELSE B.PROPERTY_VALUE END)

Much messier, but May be more index friendly

Only the B table values are being altered
a SCAN on B is necessary
an INDEX SEEK on A is now possible

+1 for data cleansing upfront. Otherwise, if we insist on loading shonky data we have to handle it *every time we use it*. — APC, Nov 29 '11 at 10:46
Then you really, Really, want to start cleaning up your data when it's being loaded. That way you can encapsulate your "clean-up" code in one place, AND using that data then becomes ALOT more performant. — MatBailie, Nov 29 '11 at 21:53

score 3 · Answer 2 · answered Nov 29 '11 at 10:06

3

You can easily remove trailing slashes using the RTRIM function:

...
AND RTRIM(A.PROPERTY_VALUE,'/') = RTRIM(B.PROPERTY_VALUE,'/')

answered Nov 29 '11 at 10:06

Erich Kitzmueller

36,381
5
80
102

score 3 · Answer 3 · answered Nov 29 '11 at 10:07

3

SELECT A.GUID as SOURCE_GUID, 
       B.GUID as DESTINATION_GUID 
FROM   tableA A,
       tableB B
WHERE  A.PROPERTY_NAME = "URL"
AND    A.PROPERY_NAME = B.PROPERTY_NAME
AND    RTRIM(A.PROPERTY_VALUE, '/') = RTRIM(B.PROPERTY_VALUE, '/')

answered Nov 29 '11 at 10:07

Xophmeister

8,884
4
44
87

...I take it you realise that you need `AND`s in the `where` clause. It might be a bit more efficient to do an inner join rather than a cross join, too. – Xophmeister Nov 29 '11 at 10:08
He has `AND` at the end of the lines. – MatBailie Nov 29 '11 at 10:16
Xophmeister: At least in the Oracle world, that's just syntactic sugar, the query optimizer takes care of the execution plan anyway. – Erich Kitzmueller Nov 29 '11 at 14:38

How to ignore trailing slash while joining two string columns in SQL

3 Answers3