How can I replace query results less than a certain length with NULL?-CodePudding

I have the following statements in my SELECT query that properly formats phone numbers that were not validated correctly in the database...it's ugly, but it works:

COALESCE(
               SUBSTRING(STUFF(STUFF(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(OFFICE_PHONE,'-',' '),',',' '),' ',''), '(', ''), ')', ''),
  4,0,'-'),8,0,'-'), 1, 12),
               SUBSTRING(STUFF(STUFF(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(MOBILE_PHONE,'-',' '),',',' '),' ',''), '(', ''), ')', ''),
  4,0,'-'),8,0,'-'), 1, 12),
               SUBSTRING(STUFF(STUFF(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FIELD_PHONE,'-',' '),',',' '),' ',''), '(', ''), ')', ''),
  4,0,'-'),8,0,'-'), 1, 12)) AS ValidPhoneNumber,

This query removes any "bad" characters and makes sure a number looks like: 123-456-7890

However, there are a few entries that are less than 12 characters; like 123-983-12....

How can I replace entries less than 12 characters in length with NULL?

Thanks!

CodePudding user response：

I fear that you will have to repeat the ugly expressions like this:

CASE WHEN LEN(ugly_expression) >= 12 THEN ugly_expression END

Unless you make a stored function with the phone number sanitizer.

COALESCE(
    CASE WHEN LEN( dbo.SanitizePhone(OFFICE_PHONE) ) >= 12
    THEN dbo.SanitizePhone(OFFICE_PHONE) END,
    CASE WHEN LEN( dbo.SanitizePhone(MOBILE_PHONE) ) >= 12
    THEN dbo.SanitizePhone(MOBILE_PHONE) END,
    CASE WHEN LEN( dbo.SanitizePhone(FIELD_PHONE) ) >= 12
    THEN dbo.SanitizePhone(FIELD_PHONE) END
) AS ValidPhoneNumber

Note that if there is no ELSE case in the CASE expression and the THEN case does not apply, NULL will be returned automatically.

See: SQL CASE Expression.

You could also get the sanitized phone numbers in a sub-select

SELECT
  COALESCE(
    CASE WHEN LEN(phone1) >= 12 THEN phone1 END,
    CASE WHEN LEN(phone2) >= 12 THEN phone2 END,
    CASE WHEN LEN(phone3) >= 12 THEN phone3 END
  ) AS ValidPhoneNumber,
  ...
FROM (
    SELECT
      STUFF(STUFF(REPLACE(...OFFICE_PHONE...))) AS phone1,
      STUFF(STUFF(REPLACE(...MOBILE_PHONE...))) AS phone2,
      STUFF(STUFF(REPLACE(...FIELD_PHONE...))) AS phone3,
      ...
    FROM ...
) phones