SQL SQL如何截断字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3374763/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL how to cut off string
提问by user380432
I have a column of strings with a city, state and number in each.
我有一列字符串，每个字符串都有一个城市、州和数字。
SPOKANE, WA 232/107
LAS VEGAS, NV 232/117
PORTLAND, OR 232/128
There are many more than just that, but I am wondering how either I could cut off the numbers in this column and just show the city and state or -even better- cut off the numbers and make city and state a separate column.
还有很多，但我想知道如何删除此列中的数字并仅显示城市和州，或者 - 甚至更好地 - 剪切数字并将城市和州作为单独的列。
The column is in the same format all the way down for all the different records.
对于所有不同的记录，该列一直采用相同的格式。
Thanks!
谢谢！
回答by Fosco
Without doing all of the work for you...
无需为您完成所有工作...
City: A substring of the column from position 0, to the first occurence of a comma - 1.
State: A substring of the column from 2 positions after the first occurence of a comma, to the next position that is a space... trimmed.
城市：从位置 0 到第一次出现逗号 - 1
的列的子字符串。状态：从第一次出现逗号后的 2 个位置到下一个空格的列的子字符串......修剪。
see: SUBSTRING(), CHARINDEX(), PATINDEX()
见：SUBSTRING(), CHARINDEX(),PATINDEX()
回答by Ash Burlaczenko
To remove the numbers at end use the substring method, something like this.
要删除末尾的数字，请使用 substring 方法，类似这样。
@str = SUBSTRING(@str, LEN(@str)-7, 7)
To separate the city and state you'll need some kind of split function but I can't remember the syntax off top of my head sorry.
要将城市和州分开，您需要某种拆分功能，但抱歉，我记不起语法了。
回答by Mark Byers
I already figured it out and had written the SQL... then I saw Fosco's answer, but since I have it I might as well post it anyway:
我已经想通了并编写了 SQL ......然后我看到了Fosco 的答案，但既然我有它，我还是可以发布它：
SELECT
LEFT(yourcolumn, CHARINDEX(',', yourcolumn) - 1) AS City,
RIGHT(LEFT(yourcolumn, CHARINDEX(',', yourcolumn) + 3), 2) AS State
FROM yourtable
The difference between this algorithm and Fosco's is that this assumes that the state is exactly 2 letters. If that is not always true then you should use the other answer.
该算法与 Fosco 的不同之处在于，它假设状态正好是 2 个字母。如果这并不总是正确的，那么您应该使用其他答案。
回答by dave
I think this will break your field down in all the ways suggested so far...
我认为这将以迄今为止建议的所有方式打破你的领域......
SELECT
substring([field], 1, patindex('%[1-9]%', [field])-1) as [CITY_AND_STATE],
substring([field], 1, charindex(',', [field])-1) as [CITY_ONLY],
substring([field], charindex(',', [field]), patindex('%[1-9]%', [field])-1)) as [STATE_ONLY],
substring([field], patindex('%[1-9]%', [field]), len([field])) as [NUMBERS_ONLY]
FROM
[table]
回答by Darryl Peterson
Consider using the REVERSE function. If you reverse the string, then the position of the first space in the reversed string can be subtracted from the length of the normal string to calculate the starting position of NNN/MMM.
考虑使用 REVERSE 函数。如果反转字符串，那么可以用正常字符串的长度减去反转字符串中第一个空格的位置来计算NNN/MMM的起始位置。
The method has worked well for me for parsing file paths. Reversing the string and looking for the first "\" in the reversed string indicates where the path ends and the file name begins.
该方法在解析文件路径方面对我来说效果很好。反转字符串并查找反转字符串中的第一个“\”表示路径结束和文件名开始的位置。
If your data is as well formatted as you indicate, this method may be more trouble than some others. But when the beginning data is "haphazard", this method can really simplify things.
如果您的数据格式与您指出的一样好，则此方法可能比其他方法更麻烦。但是当开始的数据是“杂乱无章”的时候，这个方法确实可以简化事情。
回答by AllenG
EDITWhoops. Just saw that the question was how, not if you should. That's what I get, I guess.
编辑哎呀。刚刚看到问题是如何，而不是是否应该。这就是我得到的，我想。
Other answers here are looking at substring(), so I'll leave that part of your question alone.
此处的其他答案正在查看 substring()，因此我将不理会您问题的那部分。
As far as splitting them into a different column: If the xxx/yyy does not have any direct correlation to City and State, then I would definately move those to a different column. Indeed, I would have at least three columns, here: City, State, [WhateverYouCallThatCode]
至于将它们分成不同的列：如果 xxx/yyy 与 City 和 State 没有任何直接关系，那么我肯定会将它们移到不同的列。事实上，我至少会有三列，在这里：城市、州、[WhateverYouCallThatCode]
Continued EDITIf they'll always be at the right hand of your column, how about Right(7, [Column])?
继续编辑如果他们总是在你的专栏的右手边，怎么样Right(7, [Column])？
