Search code examples
sqlhive

Extracting a number of X length from column where the X criteria is stored in another column

I have the follow Hive tables where I needed to extract the postcode from Address1 in Table_1 based on checking for a number with a specific length where this value is from Table_2. E.g. the country US has '5' in Postal field. Hence the criteria is to go to Address1 where Cty is 'US' to find for a number of 5 length. The desired output will be published in Post_Code field in Output table

I am stuck at the case statement where I figured I need a RLIKE to check for a number with 5 length from Address1 , however I am stuck at getting the criteria from another table. Would appreciate your help on what would be the cleanest or easiest code to achieve this.

SELECT A.*, B.COUNTRY, CODE, 
case when ???
FROM Table_1 A left join Table_2 B 
where 
A.Cty= B.Cty

Table_1

Address1 Cty
245 laguna niguel 92677 US
942456 AMK 423 SG
Tuas 02-22 098733 SG
Tuas 098733 12-22 SG

Table_2

Cty Postal
US 5
SG 6
.... ...

Output

Address1 Cty Postal_Code
245 laguna niguel 92677 US 92677
AMK 423 942456 SG 942456
Tuas 02-22 098733 SG 098733
Tuas 12-22 98733 SG -
Solution

  • For search fixed length numeric part of string, we can replace all digits to one (in example '9')

    translate(address1,'0123456789','9999999999')

    result is

    245 laguna niguel 92677
999 laguna niguel 99999

    and find position of substring like '99999'.

    See example

    select address1, t1.cty,postal, Postal_code
  ,substring(address1
     ,strpos(translate(address1,'0123456789','9999999999'),substring('9999999999',1,postal))
     ,postal) code
from Table_1 t1
left join Table_2 t2 on t2.Cty=t1.Cty
;
    address1 cty postal postal_code
    245 laguna niguel 92677 US 5 92677
    Tuas 098733 12-22 SG 6 098733
    Tuas 02-22 098733 SG 6 098733
    942456 AMK 423 SG 6 942456

    fiddle