SQL SYNTAX HELP PLEASE - Easy question if you can

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 25, 2017 at 12:12 pm

  #334600

  Select
COALESCE (MemberHomePhoneNbr, AlternatePhone, CellPhone ) as bestphone
FROM
mhpdw2.[EDW].[MEMBER].DMEMBER DMEM

  How can i modify this to always return only a tel number that is 10 in length.

  Our database has some garbage tel numbers listed. I only need a valid tel #

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 25, 2017 at 12:17 pm

  #1924940

  mw112009 - Wednesday, January 25, 2017 12:12 PM

  Select
  COALESCE (MemberHomePhoneNbr, AlternatePhone, CellPhone ) as bestphone
  FROM
  mhpdw2.[EDW].[MEMBER].DMEMBER DMEM

  How can i modify this to always return only a tel number that is 10 in length.

  Our database has some garbage tel numbers listed. I only need a valid tel #

  No need... I figured....

  
Select

  bestphone =
CASE
WHEN LEN(MemberHomePhoneNbr) = 10 THEN MemberHomePhoneNbr
WHEN LEN(AlternatePhone) = 10 THEN AlternatePhone
WHEN LEN(CellPhone) = 10 THEN CellPhone
ELSE
NULL
END

  FROM
mhpdw2.[EDW].[MEMBER].DMEMBER DMEM


• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 25, 2017 at 12:24 pm

  #1924944

  Even better, make sure that you only have numbers.
  
SELECT
  bestphone = CASE
      WHEN MemberHomePhoneNbr LIKE REPLICATE('[0-9]',10) THEN MemberHomePhoneNbr
      WHEN AlternatePhone LIKE REPLICATE('[0-9]',10) THEN AlternatePhone
      WHEN CellPhone LIKE REPLICATE('[0-9]',10) THEN CellPhone
      END
FROM mhpdw2.[EDW].[MEMBER].DMEMBER DMEM


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 25, 2017 at 12:30 pm

  #1924949

  Luis Cazares - Wednesday, January 25, 2017 12:24 PM

  Even better, make sure that you only have numbers.
  
SELECT
  bestphone = CASE
      WHEN MemberHomePhoneNbr LIKE REPLICATE('[0-9]',10) THEN MemberHomePhoneNbr
      WHEN AlternatePhone LIKE REPLICATE('[0-9]',10) THEN AlternatePhone
      WHEN CellPhone LIKE REPLICATE('[0-9]',10) THEN CellPhone
      END
FROM mhpdw2.[EDW].[MEMBER].DMEMBER DMEM


  Not sure how the "LIKE REPLICATE" works .....BUT does the 10 gurantee that the length is 10 digits

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 25, 2017 at 2:01 pm

  #1924960

  mw112009 - Wednesday, January 25, 2017 12:30 PM

  Luis Cazares - Wednesday, January 25, 2017 12:24 PM

  Even better, make sure that you only have numbers.
  
SELECT
  bestphone = CASE
      WHEN MemberHomePhoneNbr LIKE REPLICATE('[0-9]',10) THEN MemberHomePhoneNbr
      WHEN AlternatePhone LIKE REPLICATE('[0-9]',10) THEN AlternatePhone
      WHEN CellPhone LIKE REPLICATE('[0-9]',10) THEN CellPhone
      END
FROM mhpdw2.[EDW].[MEMBER].DMEMBER DMEM


  Not sure how the "LIKE REPLICATE" works .....BUT does the 10 gurantee that the length is 10 digits

  It's not hard to find out. You can either read about REPLICATE on BOL: https://msdn.microsoft.com/en-us/library/ms174383.aspx
  Or you can see what happens when you use it in a SELECT.
  
SELECT REPLICATE('[0-9]',10)

  It's basically like writing WHEN MemberHomePhoneNbr LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN MemberHomePhoneNbr
  Without having to count each time to remember the length.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2