CODEWITHSUNDEEP
sqloracle-databaseslashtrailing
Ram
Ram

Reputation: 3114

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

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.

Upvotes: 2

Views: 2148

Answers (3)

MatBailie
MatBailie

Reputation: 86735

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

Upvotes: 7

Xophmeister
Xophmeister

Reputation: 9211

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, '/')

Upvotes: 3

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

You can easily remove trailing slashes using the RTRIM function:

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

Upvotes: 3

Related Questions