mySQL Substring(String, Start, Length) 未产生正确的子字符串

发布于 2025-01-10 06:10:42 字数 986 浏览 0 评论 0原文

我正在尝试获取 HouseAddress EX 的城市子字符串： '1808 FOX CHASE DR, GOODLETTSVILLE, TN'

在这种情况下，城市将为 GOODLETTSVILLE

当我调用子字符串函数

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

它不断产生“GOODLETTSVILLE, TN”，而不仅仅是“GOODLETTSVILLE”。

我什至通过运行 SELECT SUBSTRING(HouseAddress, LENGTH(HouseAddress)-3) AS Test FROM housing_data; 来检查确定 Length(OwnerAddress)-3 是否正在摘录 ', TN' 部分。

我无法找出问题所在，并且已经尝试了一个小时。我只是想知道为什么当我的其他子字符串函数工作正常时这不起作用？

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

魂牵梦绕锁你心扉 2025-01-17 06:10:42

Substring_index，更全面一点。

只有其中一个部分没有昏迷，这才有效

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

选择 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(SUBSTRING_INDEX(@a,',',-2),',',1) | SUBSTRING_INDEX(@a,',',-1)
:------------------------ | :------------------------------------------------ | ：--------------------------
1808 福克斯追逐博士 |古德利茨维尔 |总氮

db<>fiddle 此处

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

回复收藏 0 原文

云淡风轻 2025-01-17 06:10:42

我只是想知道为什么当我的其他子字符串时这不起作用
功能正常吗？

字符串 '1808 FOX CHASE DR, GOODLETTSVILLE, TN' 的长度为 37，因此表达式 Length(OwnerAddress) - 3 的计算结果为(37 - 3 =) 34.

字符串 '1808 FOX CHASE DR 的子字符串， GOODLETTSVILLE, TN' 在第一个 , 之后开始（这是 Locate(',' , HouseAddress) + 1 返回的内容）并扩展到下一个33 个字符（因此包含 34 个字符）为 ' GOODLETTSVILLE, TN' （少于 34 个字符，因为字符串结尾为

我怀疑您认为 Substring(String, Start, Length) 的 3d 参数是 Length，指的是从开头开始的长度字符串的，但事实并非如此。
长度从第二个参数开始定义的位置开始。