Case when based on first few characters

user_7886073 0
Viewed 60

I have Table A that looks like this:

Name   Phone
John   1111231234
Joe    1111231235
Jack   2221231234
Jenny  2224321234
Jody   3323214211

and Table B that looks like this:

AreaCode
111         
111
222
222

How do I return a result that looks like this? I essentially want to return AreaCode if the first 3 numbers/characters from the column 'Phone' exist in the column 'AreaCode' in table B...

Name   Phone       AreaCode
John   1111231234  111
Joe    1111231235  111
Jack   2221231234  222
Jenny  2224321234  222
Jody   3323214211  null
case teradata join sql
4 Answers

Use a left join to table b, joining where the phone starts with the areacode:

select
    name,
    phone,
    areacode
from tableA
left join tableB on phone like concat(areacode, '%')
user_2561960

I used distinct to avoid repeated areacodes that will bring you duplicated results. If you store areacode an phone as numeric you can omit the cast

select a.*, b.AreaCode
from TableA a left join (select distinct areacode from tableb) b 
    on left(cast(a.Phone as varchar(20)),3)=cast(b.AreaCode as varchar(20))
user_29944120

In teradata, you may need something like this

select
    name,
    phone,
    areacode
from tableA
left join tableB on SUBSTRING(phone FROM 1 FOR 3) = areacode
user_63436170

As some values in column phone exceeds the max allowed limit for INTEGER i.e. 2147483647, so i assume that BIGINT is the datatype. In this case below is the query that will return your desired result.

SELECT DISTINCT t1.Name,
                t1.Phone,
                t2.areacode
FROM t1
LEFT JOIN t2 ON substring(t1.Phone
                          FROM 11
                          FOR 3) = t2.areacode
ORDER BY 3 DESC;

substring impliticitly cast bigint to 20 space variable character left justified as bigint requires 20 character, so the starting point for substring is 11. Also teradata implicitly compare character with INT, so no need to cast areacode

Other option is to use trim before substring to remove leading spaces as below.

SELECT DISTINCT t1.Name,
                t1.Phone,
                t2.areacode
FROM t1
LEFT JOIN t2 ON substring(trim(t1.Phone)
                          FROM 1
                          FOR 3) = t2.areacode
ORDER BY 3 DESC;

DISTINCT is used to avoid duplicates, as area code has duplicate values, so joining it to the phone record table will create duplicate rows.

Result:

Name    Phone             areacode
----------------------------------
Jack    2,221,231,234     222
Jenny   2,224,321,234     222
Joe     1,111,231,235     111
John    1,111,231,234     111
Jody    3,323,214,211     ?

P.S. As substring and trim functions are same in Teradata and MySQL, you can check the demo here

Hope this will help :-)

user_84156700
Related