Need to condense three columns into one selecting a specific number from one of the three

Question

I have three columns. each column can be null, have a telephone number starting 01 or a telephone number starting 07. I want to work out which column has an 07 number and show just that result in a single column. if there is no column starting with 07 I want to skip the record.

I have been trying this but cant get it to select column 3: -

SELECT ID_no, tel_no1, tel_no2, tel_no3,
coalesce(
  CASE WHEN (tel_no1 like '01%' or tel_no1 = null) THEN tel_no2 ELSE NULL END,
  CASE WHEN (tel_no2 like '01%' or tel_no2 = null) THEN tel_no3 ELSE NULL END)

from Table where ID_no = 50032

`tel_no1 = null` [Is there any difference between IS NULL and =NULL](https://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null) — Lukasz Szozda, Oct 04 '17 at 16:28

score 0 · Accepted Answer · answered Oct 04 '17 at 16:30

I think this is a basic case with some where conditions. Most databases support left(). Otherwise substr()/substring() works for the where condition:

select t.*,
       (case when tel_no1 like '07%' then tel_no1
             when tel_no2 like '07%' then tel_no2
             when tel_no3 like '07%' then tel_no3
        end) as telno_07
from t
where id_no = 50032 and
      '07' in (left(tel_no1, 2), left(telno2, 2), left(telno3, 2));

This returns the first matching "07" number.

Or, the more standard where condition would be:

where . . . and
      (tel_no1 like '07%' or tel_no2 like '07%' or tel_no3 like '07%')

Thank you @Gordon Linoff, that did it. seems I was making it too complicated. — Simon Farnworth, Oct 04 '17 at 16:36

Need to condense three columns into one selecting a specific number from one of the three

1 Answers1