I am new to SQL and had training to enable me to use SSRS in March but we also have an oracle DB.

I need to extract phone numbers which can be held in columns called home phone, work phone or mobile phone but I want to only extract if the number starts 07 but also limit to 11 digits. The issue here is the number could be in all or any 3 of the columns.

If the number starts with anything other than 07 or if the data has text, I want to replace this with a blank.

I have looked at using

'''Select home phone, Work phone, Mobile no

From DB Where home phone like '07'% And work phone like '07'% And mobile no like '07'%'''

But this massively reduces the data set as expected.

I have looked at replace and regexp-replace but can't quite get my head around this bit at the minute. Any help much appreciated, I think want to export all of the data just have a blank if it's not an 11 digit mobile number.

CodePudding user response：

You should put OR instead of AND in your query. Since you mentioned you want to export data only so I think the query is to format the phone number as below:

SELECT
  CASE
    WHEN SUBSTR(HOME_PHONE, 1, 2) = '07' --START WITH '07'
        AND LENGTH(HOME_PHONE) = 11      --11 DIGITS
        AND LENGTH(TRIM(TRANSLATE('HOME_PHONE', '  -.0123456789', ' '))) IS NULL --CONTAIN ONLY NUMBER
      THEN HOME_PHONE
    ELSE '' --RETURN BLANK IF CONDITION NOT MATCHED
  END AS HOME_PHONE
FROM DATA_TBL

You can apply the same to select WORK_PHONE and MOBILE_PHONE.

If this is not what you are looking for, please give sample data and desired output so it would be easier for me to replicate.