SQL如何截断字符串 [英] SQL how to cut off string

问题描述

我有一列字符串，每个字符串包含一个城市、州和数字.

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.

谢谢！

推荐答案

无需为您完成所有工作...

Without doing all of the work for you...

城市:列的子字符串，从位置 0 到逗号的第一次出现 - 1.
状态:列的子字符串，从逗号第一次出现后的 2 个位置到下一个空格的位置......修剪.

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.

参见:SUBSTRING()、CHARINDEX()、PATINDEX()

这篇关于SQL如何截断字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！