193. Valid Phone Numbers - Detailed Explanation

Problem Statement

You are given a table called PhoneNumbers that contains a column phone. The task is to write a SQL query to find all valid phone numbers from the table. A valid phone number is defined as a string that consists of exactly 10 digits (0-9) with no additional characters, spaces, or symbols.

Example

Suppose the PhoneNumbers table contains the following data:

• Valid Phone Numbers:

  • "1234567890" → Exactly 10 digits.
  • "0123456789" → Exactly 10 digits (leading zero is acceptable).

• Invalid Phone Numbers:

  • "123-456-7890" → Contains dashes.
  • "123456789" → Only 9 digits.
  • "(123)4567890" → Contains parentheses.

The expected output should include only the rows with valid phone numbers, i.e., "1234567890" and "0123456789".

Hints

1. Use Regular Expressions:
   The SQL dialect (MySQL in this case) provides a REGEXP operator that can be used to match patterns.

2. Exact Match Pattern:
   Create a pattern that ensures the entire string contains exactly 10 digits. Use the caret ^ to indicate the start of the string and the dollar sign $ to indicate the end. The pattern should look like ^[0-9]{10}$.

Approach Overview

Using Regular Expression Matching

• Step 1:
  Select the phone column from the PhoneNumbers table.
• Step 2:
  Use the WHERE clause with the REGEXP operator to filter rows. The regular expression '^[0-9]{10}$' ensures that:
  • ^ marks the beginning of the string.
  • [0-9]{10} matches exactly 10 digits.
  • $ marks the end of the string.
• Step 3:
  Return the filtered rows that match the valid phone number format.

SQL Query

SELECT phone
FROM PhoneNumbers
WHERE phone REGEXP '^[0-9]{10}$';

Detailed Step-by-Step Explanation

1. Select the Column:

   • We begin with SELECT phone FROM PhoneNumbers to retrieve all phone numbers from the table.

2. Apply the Regular Expression Filter:

   • The condition phone REGEXP '^[0-9]{10}$' is applied in the WHERE clause.
   • Pattern Breakdown:
     • ^: Ensures that the match starts at the beginning of the string.
     • [0-9]{10}: Matches exactly 10 digits (each digit between 0 and 9).
     • $: Ensures that the match ends at the end of the string.
   • This pattern guarantees that only strings with exactly 10 digits, and no other characters, are selected.

3. Return Valid Results:

   • The query returns only those rows where the phone value is a valid 10-digit number.