CODEWITHSUNDEEP
sqloracle-database
jackie21
jackie21

Reputation: 337

Join considering length of characters

I am trying to do a left join based on the number of characters. In the destination_postal_code column I hace a more deailed postcode value. In the dest_postal_code_prefix I have only the first few digits of a postcode. When I join I would like to output to consider this:

destination_postal_code     dest_postal_code_prefix
2345                        23
3356                        33
5672                        567

For now I have :

SELECT DISTINCT 
p.destination_postal_code
FROM posimorders.atrops_ddl.o_slam_packages p
LEFT JOIN posimorders.sc_execution_eu.o_transit_time_pad t
ON p.destination_postal_code = t.dest_postal_code_prefix

The expected output would be 

destination_postal_code 
2345                        
3356                        
5672

In the output it can be seen that, when my prefix is 23, I want all psotcodes begining with 23.. to be in mu output and so on

Upvotes: 0

Views: 49

Answers (2)

Adder
Adder

Reputation: 5868

Maybe this, using the LIKE operator:

SELECT DISTINCT 
p.destination_postal_code, t.dest_postal_code_prefix
FROM posimorders.atrops_ddl.o_slam_packages p
LEFT JOIN posimorders.sc_execution_eu.o_transit_time_pad t
ON p.destination_postal_code like t.dest_postal_code_prefix||'%';

If you have overlap, you might want to have the longest matches first:

SELECT DISTINCT 
p.destination_postal_code, t.dest_postal_code_prefix
FROM posimorders.atrops_ddl.o_slam_packages p
LEFT JOIN posimorders.sc_execution_eu.o_transit_time_pad t
ON p.destination_postal_code like t.dest_postal_code_prefix||'%'
ORDER BY LENGTH(t.dest_postal_code_prefix) DESC
;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270011

If your prefixes don't overlap, then you can just use LIKE:

SELECT DISTINCT p.destination_postal_code, t.dest_postal_code_prefix
FROM posimorders.atrops_ddl.o_slam_packages p LEFT JOIN
     posimorders.sc_execution_eu.o_transit_time_pad t
     ON p.destination_postal_code LIKE t.dest_postal_code_prefix || '%';

However, if they overlap (say "12" and "123"), then you probably want the longest one. In that case, use aggregation along with LIKE:

SELECT p.destination_postal_code, MAX(t.dest_postal_code_prefix) as dest_postal_code_prefix
FROM posimorders.atrops_ddl.o_slam_packages p LEFT JOIN
     posimorders.sc_execution_eu.o_transit_time_pad t
     ON p.destination_postal_code LIKE t.dest_postal_code_prefix || '%'
GROUP BY p.destination_postal_code;

Upvotes: 1

Related Questions