MySQL多条件查询：提取无逗号分隔街道字段的SQL方案求助

Got it, let's sort out this issue! The problem with your current query is that when there's no comma in the physicalDeliveryOfficeName value, LOCATE(',', physicalDeliveryOfficeName) returns 0. Using that in LEFT(..., 0-1) results in an empty string because you're asking for a negative length.

Here's how to fix it by handling both comma-containing and comma-free cases:

Option 1: Using IF Function (MySQL-compatible) #

SELECT 
  IF(LOCATE(',', physicalDeliveryOfficeName) > 0,
     LEFT(physicalDeliveryOfficeName, LOCATE(',', physicalDeliveryOfficeName) - 1),
     physicalDeliveryOfficeName) AS street_name
FROM contacts
GROUP BY street_name;

Option 2: Using CASE Statement (More Readable, Works Across Most Databases) #

SELECT 
  CASE
    WHEN LOCATE(',', physicalDeliveryOfficeName) > 0 THEN
      LEFT(physicalDeliveryOfficeName, LOCATE(',', physicalDeliveryOfficeName) - 1)
    ELSE
      physicalDeliveryOfficeName
  END AS street_name
FROM contacts
GROUP BY street_name;

How It Works #

• When LOCATE finds a comma (returns a value greater than 0), we extract everything before the comma just like your original query did.
• When there's no comma (LOCATE returns 0), we just use the entire physicalDeliveryOfficeName value since it's already the street name.

If you're using a database like SQL Server instead of MySQL, replace LOCATE with CHARINDEX—the logic stays exactly the same.

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