Search code examples
mysqlsubstring

mySQL Substring(String, Start, Length) Not Resulting in Correct Substring

I am trying to get the city substring of an HouseAddress EX: '1808 FOX CHASE DR, GOODLETTSVILLE, TN'

In this case the city will be GOODLETTSVILLE

When I call the substring function

Substring(HouseAddress, Locate(',' , HouseAddress) + 1, Length(OwnerAddress) - 3) AS City FROM housing_data;

It keeps resulting in 'GOODLETTSVILLE, TN', rather than just 'GOODLETTSVILLE'.

I even checked sure Length(OwnerAddress)-3 is snippeting the ', TN' part of by running a SELECT SUBSTRING(HouseAddress, LENGTH(HouseAddress)-3) AS Test FROM housing_data;

I can't find out what's wrong and have been trying for an hour. I just want to know why this doesn't work when my other substring functions work fine?

SELECT SUBSTRING(HouseAddress, LENGTH(HouseAddress)-2, LENGTH(HouseAddress)) AS STATE FROM housing_data; -- Retrieves the State Perfectly OK 'TN'

SELECT SUBSTRING(HouseAddress, 1, LOCATE(',', HouseAddress)-1) AS STREET FROM housing_data;
-- Retrieves the Street Perfectly OK '1808  FOX CHASE DR'

SELECT SUBSTRING(HouseAddress, LOCATE(',', HouseAddress) + 1, LENGTH(HouseAddress) -3) AS CITY FROM housing_data; -- Doesn't work, result is GOODLETTSVILLE, TN
Solution

  • Substring_index, is a bit more comprehensive.

    Thsi wuld only work, if one of the parts don't have acoma in it

    SET @a =  '1808 FOX CHASE DR, GOODLETTSVILLE, TN'

    SELECT SUBSTRING_INDEX(@a,',',1),SUBSTRING_INDEX(SUBSTRING_INDEX(@a,',',-2),',',1),SUBSTRING_INDEX(@a,',',-1)

    SUBSTRING_INDEX(@a,',',1) | SUBSTRING_INDEX(SUBSTRING_INDEX(@a,',',-2),',',1) | SUBSTRING_INDEX(@a,',',-1)
:------------------------ | :------------------------------------------------ | :-------------------------
1808 FOX CHASE DR         |  GOODLETTSVILLE                                   |  TN

    db<>fiddle here