CREATE FUNCTION dbo.QualifyPhoneNumber (@OrigPhoneNumber VARCHAR (30), @CountryNumber INT)  
RETURNS VARCHAR (30) AS  
BEGIN 
/*
  NB20050210: Format length based on country phone format and ignore several phones in the field
  NB20050131: Format based on country 
  NB20050128: Created
  NOTE: Format the the phone number for compare and for updates

@CountryNumber:
=0 only numbers (for compare) 
>0 international phone numbers (for updates)
*/
  DECLARE @NewPhoneNumber VARCHAR(30)
  IF LEN(@OrigPhoneNumber) > 4
  BEGIN  
    DECLARE @OldPhoneNumber VARCHAR(30), @Position INT, @Position1 INT, @CharASCIINumber INT, @PhoneLength INT,  @PhoneFormat VARCHAR(30)
    SET @Position = 0
    SET @Position1 = 0 
    SET @NewPhoneNumber = ''
    --Remove spaces
    SET @OldPhoneNumber = REPLACE(@OrigPhoneNumber, ' ', '')
    IF ISNULL(@CountryNumber, 0) = 0 
      SET @PhoneLength = 0
    ELSE 
--      SELECT @PhoneFormat = PhoneFormat, @PhoneLength = (CASE WHEN CountryCode LIKE 'DK' THEN 6 ELSE LEN(SUBSTRING(PhoneFormat, CHARINDEX('_', PhoneFormat)+1, LEN(PhoneFormat))) END)  FROM Countries (NOLOCK) WHERE CountryNumber= @CountryNumber
      SELECT @PhoneLength = (CASE WHEN CountryCode LIKE 'DK' THEN 8 ELSE LEN(PhoneFormat)-1 END)  FROM Countries WITH (NOLOCK) WHERE CountryNumber= @CountryNumber
    --Remove invalid characters (only numbers allowed until further)  
    WHILE @Position <= LEN(@OldPhoneNumber)
    BEGIN
      SELECT @Position = @Position + 1
      SET @CharASCIINumber = UNICODE(SUBSTRING(@OldPhoneNumber, @position, 1))
      /*
      IF @PhoneLength = 0
      BEGIN 
        IF (@CharASCIINumber >= 48) AND (@CharASCIINumber <= 57) --numbers
          SET @NewPhoneNumber = @NewPhoneNumber + SUBSTRING(@OldPhoneNumber, @Position, 1) 
        ELSE
        BEGIN
          --Contains several phonenumbers: return only the first phone number
          IF @Position1 = 0 
            SET @Position1 = @Position
          IF @Position1 >= 8 --based on rønbjerg data 5000 records
            BREAK
        END
      END
      ELSE
      BEGIN
        IF ((@CharASCIINumber >= 48) AND (@CharASCIINumber <= 57)) OR (@CharASCIINumber = 43)--only numbers (for compare) or international phone numbers (for updates)
          SET @NewPhoneNumber = @NewPhoneNumber + SUBSTRING(@OldPhoneNumber, @Position, 1) 
        ELSE
        BEGIN
          --Contains several phonenumbers: return only the first phone number
          IF @Position1 = 0 
            SET @Position1 = @Position
          IF @Position1 >= 7 --several or local extension of phone numbers
            SET @NewPhoneNumber = @NewPhoneNumber + SUBSTRING(@OldPhoneNumber, @Position, 1) 
        END
      END
*/--nb20050210
        IF ((@CharASCIINumber >= 48) AND (@CharASCIINumber <= 57)) OR (@CharASCIINumber = 43)--only numbers (for compare) or international phone numbers (for updates)
          SET @NewPhoneNumber = @NewPhoneNumber + SUBSTRING(@OldPhoneNumber, @Position, 1) 
    END
    IF LEN(@NewPhoneNumber) > 4
    BEGIN
      IF @PhoneLength > 0
      BEGIN
      --Insert the Country Call part of the PhoneNumber
--        SELECT @PhoneFormat = PhoneFormat FROM Countries (NOLOCK) WHERE CountryNumber = @CountryNumber AND NOT CountryCode LIKE 'DK'
        IF NOT ISNULL(@PhoneFormat,'') = ''
        BEGIN
          SET @Position = 2
          SET @OldPhoneNumber = ''  
          WHILE @Position <= LEN(@PhoneFormat)
          BEGIN
            SELECT @Position = @Position + 1
            SET @CharASCIINumber = UNICODE(SUBSTRING(@PhoneFormat, @Position, 1))
            IF (@CharASCIINumber >= 48) AND (@CharASCIINumber <= 57) --numbers
              SELECT @OldPhoneNumber = @OldPhoneNumber + SUBSTRING(@PhoneFormat, @Position, 1) 
            ELSE
            BEGIN 
              --compare dialing code with existing phonenumber and do replace or insert 
              DECLARE @CharIndex INT
              SET @CharIndex = CHARINDEX('00' + @OldPhoneNumber, @NewPhoneNumber)
              IF (@CharIndex <> 1)
                SELECT @NewPhoneNumber = '00' + @OldPhoneNumber + @NewPhoneNumber 
              BREAK
            END
          END
        END
        SET @NewPhoneNumber = SUBSTRING(@NewPhoneNumber, 1, @PhoneLength) 
      END      
    END
    ELSE
      SET @NewPhoneNumber = NULL
  END
  ELSE
    SET @NewPhoneNumber = NULL
  RETURN @NewPhoneNumber
END

