MySQL Workbench：将最后2个字符与列分开
我正在尝试从列中删除最后两个字符。我要定位的当前列已经通过分开字符串来创建，但是如下所示，“城市”列并不成功。
这就是原始外观： 在此处输入图像描述
这就是我能够从代码中输出的内容：
| StreetNumber | Street | **City** | State |
|----------------------------------------------------------------------|
| 1808 | FOX CHASE DR | **GOODLETTSVILLE TN** | TN |
| 1832 | FOX CHASE DR | **GOODLETTSVILLE TN** | TN |
| 2005 | SADIE LN | **GOODLETTSVILLE TN** | TN |
实际图片：在此处输入映像说明
这是我的代码：
select substring_index(substring_index(OwnerAddress, ' ', 1), ' ', -1) as StreetNumber,
substring(OwnerAddress, locate(' ', OwnerAddress),
(length(OwnerAddress) - (length(substring_index(OwnerAddress, ' ', 1))
+ length(substring_index(OwnerAddress, ' ', -2))))) as Street,
substring(substring_index(OwnerAddress, ' ', -2) from 1 for length(OwnerAddress)-2) as City,
substring_index(OwnerAddress, ' ', -1) as State
from nashhousing;
目标是从状态下删除状态的缩写“城市”列，因为已经有一个状态列。我以为我可以简单地-2对于最后两个字符，但是显然，这无效。我希望我已经清楚地解释了我的情况，但是如果没有，请告诉我。我不想放弃这种情况，但是我已经待了5个小时了，无法采购解决方案。请帮助，并提前感谢您！
I am trying to remove the last two characters from a column. The current column that I am targeting has already been created by separating a string, but as you'll see below, it wasn't successful for the 'City' column.
This is how the original looks:
enter image description here
This is what I've been able to output from my code:
| StreetNumber | Street | **City** | State |
|----------------------------------------------------------------------|
| 1808 | FOX CHASE DR | **GOODLETTSVILLE TN** | TN |
| 1832 | FOX CHASE DR | **GOODLETTSVILLE TN** | TN |
| 2005 | SADIE LN | **GOODLETTSVILLE TN** | TN |
actual pic:enter image description here
This is my code:
select substring_index(substring_index(OwnerAddress, ' ', 1), ' ', -1) as StreetNumber,
substring(OwnerAddress, locate(' ', OwnerAddress),
(length(OwnerAddress) - (length(substring_index(OwnerAddress, ' ', 1))
+ length(substring_index(OwnerAddress, ' ', -2))))) as Street,
substring(substring_index(OwnerAddress, ' ', -2) from 1 for length(OwnerAddress)-2) as City,
substring_index(OwnerAddress, ' ', -1) as State
from nashhousing;
The goal is to remove the state abbreviations from the 'City' column because there's a state column already. I thought I could simply -2 for the last two characters but obviously, that didn't work. I hope I've explained my situation clearly, but if not, please let me know. I don't want to give up on this situation but I've been on it for 5 hours already and can't source a solution. Please help and thank you in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
为了直接回答您的问题，您正在使用错误的字段和价值来为
substring的长度部分使用。这应该纠正您的问题：
甚至
请注意：
以这种方式进行操作的主要问题是，您假设每个条目都相同，并且您没有考虑具有两个名称的城市。即纽约市，洛杉矶，旧金山等。
这是您可能需要在MySQL之外解析的东西。由于您只需要解析它，因此您可能会写出足够的正则表达式以处理大多数情况。但是，如果准确性是您的首要任务，我建议将数据进行地理编码。
To directly answer your question, you are using the wrong field and value for the length portion of the
SUBSTRINGon the city field.This should correct your issue:
Or even
Please Note:
The major issue with doing things this way is you are assuming every entry is formatted the same, and you're not taking into account cities with two names. ie New York City, Los Angeles, San Francisco, etc.
This is something that you likely need to parse outside of MySQL. Since you only need it to be parsed, you could likely write a decent enough RegEx to handle the majority of the cases. However, if accuracy is your top priority, I would recommend geocoding the data.