如何在SQL中用正则表达式提取去除区号及分隔符后的电话号码

Got it, let's break down these two phone number processing tasks using regular expressions. I'll use SQL examples since you provided tabular data, but the regex logic translates to most programming languages (Python, JavaScript, etc.) with minor syntax tweaks.

1. Remove Country Code, Separators (- / etc.) and Keep Only the Phone Number Digits #

This task requires stripping two types of content: the leading country code (starting with + followed by digits) and any non-digit separators like - or /.

Regex Solution #

Use a regex pattern that matches both the leading country code segment and all non-digit characters, then replace them with empty strings.

For SQL (e.g., MySQL, PostgreSQL):

SELECT REGEXP_REPLACE(PHONENO, '^\+\d+[-/]?|\D', '') AS CLEANED_PHONENO
FROM your_table;

Pattern Explanation #

• ^\+\d+[-/]?: Matches the leading country code:
  • ^: Anchors the match to the start of the string
  • \+: Escapes the + character (since it's a special regex symbol)
  • \d+: Matches one or more digits in the country code
  • [-/]?: Optional match for a separator (- or /) right after the country code
• |\D: Alternate pattern that matches any non-digit character (to remove other separators in the number)

Example Output #

2. Only Remove Country Code, Keep Number Separators (If Needed) #

If you just want to strip the leading country code but keep the original separators (-, /) in the rest of the number, use a more targeted regex that only matches the country code segment.

Regex Solution #

For SQL:

SELECT REGEXP_REPLACE(PHONENO, '^\+\d+[-/]?', '') AS CLEANED_PHONENO
FROM your_table;

Pattern Explanation #

• ^\+\d+[-/]?: Same as the country code segment from the first task—this only targets the leading country code and its immediate separator (if present), leaving the rest of the string untouched.

Example Output #

(Note: If your phone numbers have separators after the country code, this will preserve them as intended.)

Edge Case Notes #

• If country codes might include spaces (e.g., +89 - 8646538468), adjust the regex to account for optional whitespace: ^\+\d+\s*[-/]?\s*
• For non-SQL languages like Python, the regex logic stays the same—just use the language's regex substitution method:

  import re
  
  # Task 1: Remove country code + all separators
  def clean_phone_task1(phone):
      return re.sub(r'^\+\d+[-/]?|\D', '', phone)
  
  # Task 2: Only remove country code
  def clean_phone_task2(phone):
      return re.sub(r'^\+\d+[-/]?', '', phone)
  

内容的提问来源于stack exchange，提问作者Bharath_Raja