我表中的数据代表具有以下数据的物理位置：市政名称，州（省/地区）以及由前缀和后缀组成的唯一ID，并由破折号（All Nvarchar）隔开。
| 名称 |
状态 |
唯一 |
| 的亚特兰大 |
乔治亚州 |
A12-1383 |
独特的仪表板并不总是处于相同的位置（可以是A1-XYZ，A1111-XYZ等）。后缀将始终是数字的。
我的方法是使用右和 charindex 的组合首先找到仪表板的索引，然后将后缀隔离到破折号的右边，然后应用最大到结果。到目前为止，我的问题是，这种组合有时会返回 -1234 或 12-1234 ，即，包括仪表板，偶尔还有一些前缀。因此，最大化显然是错误地应用的。
到目前为止，这是我的查询：
select name, max(right(uniqueid,(Charindex('-',uniqueid)))) as 'Max'
from locations
where state = 'GA' and uniqueid is not NULL
group by name
order by name ASC
这是格式不佳的行：
| 的结果 |
Max |
| Atlanta |
11-2442 |
| Savannah |
-22 |
，这是“ Max”的返回错误格式的数据，因此我隔离了功能。
charindex 正确返回仪表板的位置，包括在函数返回不良格式数据的情况下。由于破折号永远不会在同一位置，因此我无法隔离右函数，以查看是否是问题。
我是否错误地使用了这些功能？还是我的方法完全不正确？
谢谢！
The data in my table represents physical locations with the following data: A municipality name, a state(province/region), and a unique ID which is comprised of a prefix and postfix separated by a dash (all NVARCHAR).
| Name |
State |
UniqueID |
| Atlanta |
Georgia |
A12-1383 |
The dash in the UniqueID is not always in the same position (can be A1-XYZ, A1111-XYZ, etc.). The postfix will always be numerical.
My approach is using a combination of RIGHT and CHARINDEX to first find the index of the dash and then isolate the postfix to the right of the dash, and then applying a MAX to the result. My issue so far has been that this combination is sometimes returning things like -1234 or 12-1234, i.e, including the dash and occasionally some of the prefix. Because of this, the max is obviously applied incorrectly.
Here is my query thus far:
select name, max(right(uniqueid,(Charindex('-',uniqueid)))) as 'Max'
from locations
where state = 'GA' and uniqueid is not NULL
group by name
order by name ASC
This is what the results look like for the badly formatted rows:
| Name |
Max |
| Atlanta |
11-2442 |
| Savannah |
-22 |
This is returning incorrectly formatted data for 'Max', so I isolated the functions.
CHARINDEX is correctly returning the position of the dash, including in cases where the function is returning badly formatted data. Since the dash is never in the same place, I cannot isolate the RIGHT function to see if that is the problem.
Am I using these functions incorrectly? Or is my approach altogether incorrect?
Thanks!
发布评论
评论（1）
charindex正在计算-> ie之前有多少个字符左>上的字符数量无法正常工作。您需要找出可以使用len（）来完成的右上有多少个字符，以使总长度减少左侧的字符数量。CHARINDEXis counting how many chars before the-i.e. how many chars on the left hand side, so usingRIGHTwith the number of chars on the left isn't going to work. You need to find out how many chars are on the right which can be done withLEN()to get the total length less the number of chars on the left.