如何编写去除电话号码前3位的SQL SELECT查询语句

Got it, let's sort out that phone number formatting issue you're facing! Your existing query already handles the contact name perfectly—we just need to adjust how we pull the VendorPhone value to strip off the first three digits.

Basic Solution #

Assuming your VendorPhone column is stored as a string (which is standard for phone numbers, since they often have leading zeros or special characters), we can use the SUBSTRING function to start extracting from the 4th character onward:

SELECT 
  VendorContactFName + ' ' + SUBSTRING(VendorContactLName, 1, 1) + '.' AS [Contact],
  SUBSTRING(VendorPhone, 4) AS Phone 
FROM Vendors;

How this works:

• SUBSTRING(VendorPhone, 4) tells the database to skip the first 3 characters and grab everything from position 4 to the end of the string. This works across most common SQL dialects like SQL Server, MySQL, and PostgreSQL.

Handling Edge Cases #

If there's a chance some phone numbers are shorter than 3 digits (unlikely, but better safe than sorry), we can add a quick check to avoid returning empty strings or errors:

For SQL Server:

SELECT 
  VendorContactFName + ' ' + SUBSTRING(VendorContactLName, 1, 1) + '.' AS [Contact],
  CASE 
    WHEN LEN(VendorPhone) >= 3 THEN SUBSTRING(VendorPhone, 4)
    ELSE VendorPhone  -- Keep the original if it's too short
  END AS Phone 
FROM Vendors;

For MySQL (note we use CHAR_LENGTH instead of LEN here):

SELECT 
  CONCAT(VendorContactFName, ' ', SUBSTRING(VendorContactLName, 1, 1), '.') AS Contact,
  CASE 
    WHEN CHAR_LENGTH(VendorPhone) >= 3 THEN SUBSTRING(VendorPhone, 4)
    ELSE VendorPhone
  END AS Phone 
FROM Vendors;

That should do exactly what you need—return phone numbers without their first three digits while keeping your contact name formatting intact!

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