Query help

Transact-SQL
1

I have a table that has a sequence number assigned in the form of 12-12345-1. As the sequence increases, the last digit increments by 1, so the next sequence number would be 12-12345-2. What I need to do is query the table to find all sequence numbers ending with '1' that do not have a '2' or greater. The sequence number is a varchar field, if that matters.

Anyone have an idea?

2 
SELECT *
FROM YourTable T1
    LEFT JOIN YourTable T2
        ON LEFT(T1.seq_no, 8) = LEFT(T2.seq_no, 8)
            AND T1.seq_no < T2.seq_no
WHERE RIGHT(T1.seq_no, 2) = '-1'
    AND T2.seq_no IS NULL;
SELECT *
FROM YourTable T1
WHERE RIGHT(T1.seq_no, 2) = '-1'
    AND NOT EXISTS
    (
        SELECT 1
        FROM YourTable T2
        WHERE LEFT(T2.seq_no, 8) = LEFT(T1.seq_no, 8)
            AND T2.seq_no > T1.seq_no
    );

etc

3

Thanks
This would work if all sequence numbers were the same length. I should have mentioned this...apologies The sequence numbers can have between 3-5 digits in that middle segment, so your suggestions would not work in all cases. The first segment will always be 2 digits, then a '-' then the middle segment, between 3-5 digits, than another '-' then the last segment could be between 1-9999.

4 
; with 
sample_data as
(
    select    seq = '12-12345-1'    union all
    select    seq = '12-12345-9999'    union all
    select    seq = '12-99999-9999'
),
split_into_num as
(
    select    *, 
        seq1 = convert(int, parsename(replace(seq, '-', '.'), 3)),
        seq2 = convert(int, parsename(replace(seq, '-', '.'), 2)),
        seq3 = convert(int, parsename(replace(seq, '-', '.'), 1))
    from    sample_data
),
increment_it as
(
    select    *,
        ns1    = seq1 + (seq2 + (seq3 + 1) / 9999) / 99999,
        ns2    = (seq2 + (seq3 + 1) / 9999) % 99999,
        ns3    = (seq3 + 1) % 9999
    from    split_into_num
)
select    *,
    new_seq    = convert(varchar(5), ns1)
        + '-'
        + convert(varchar(5), ns2)
        + '-'
        + convert(varchar(5), ns3)
from    increment_it

/* result
seq           seq1        seq2        seq3        ns1         ns2         ns3         new_seq           
------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------------- 
12-12345-1    12          12345       1           12          12345       2           12-12345-2
12-12345-9999 12          12345       9999        12          12346       1           12-12346-1
12-99999-9999 12          99999       9999        13          1           1           13-1-1
*/