SQL Query returning incorrectly formatted data even though the function is returning the correct values

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!

Answer

CHARINDEX is counting how many chars before the - i.e. how many chars on the left hand side, so using RIGHT with 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 with LEN() to get the total length less the number of chars on the left.

SELECT RIGHT(MyColumn,LEN(MyColumn)-CHARINDEX('-',MyColumn))
FROM (
    VALUES
    ('A12ssss-1383'),
    ('A12-13834'),
    ('A12ss-138')
) X (MyColumn);