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
– APC, Commented Nov 29, 2011 at 10:48

Allan · Accepted Answer · 2011-11-29 20:10:50Z

7

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

edited Nov 29, 2011 at 20:10

Allan

17.5k5 gold badges54 silver badges73 bronze badges

answered Nov 29, 2011 at 10:07

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

APC Over a year ago

+1 for data cleansing upfront. Otherwise, if we insist on loading shonky data we have to handle it every time we use it.

MatBailie Over a year ago

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.

Erich Kitzmueller · Accepted Answer · 2011-11-29 10:06:46Z

3

You can easily remove trailing slashes using the RTRIM function:

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

answered Nov 29, 2011 at 10:06

Erich Kitzmueller

37.1k5 gold badges85 silver badges104 bronze badges

Comments

Xophmeister · Accepted Answer · 2011-11-29 10:07:12Z

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, 2011 at 10:07

Xophmeister

9,2495 gold badges52 silver badges99 bronze badges

3 Comments

Xophmeister Over a year ago

...I take it you realise that you need ANDs in the where clause. It might be a bit more efficient to do an inner join rather than a cross join, too.

MatBailie Over a year ago

He has AND at the end of the lines.

Erich Kitzmueller Over a year ago

Xophmeister: At least in the Oracle world, that's just syntactic sugar, the query optimizer takes care of the execution plan anyway.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related