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

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

CodePudding user response：

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

CodePudding user response：

I just want to know why this doesn't work when my other substring functions work fine?

The length of the string '1808 FOX CHASE DR, GOODLETTSVILLE, TN' is 37, so the expression Length(OwnerAddress) - 3 evaluates to (37 - 3 =) 34.

The substring of the string '1808 FOX CHASE DR, GOODLETTSVILLE, TN' that starts right after the first , (this is what Locate(',' , HouseAddress) 1 returns) and extends to the next 33 chars (so that 34 chars are included) is ' GOODLETTSVILLE, TN' (less than 34 chars because the end of the string is reached).

I suspect that you think that the 3d argument of Substring(String, Start, Length) which is Length, refers to the length starting from the beginning of the string, but this is not so.
The Length starts from the position defined by the 2nd argument Start.