My SQL Server 2019 table is:

CREATE TABLE [dbo].[mytable]
(
    [idnumber] [nvarchar](255) NULL
)

and I have this query:

SELECT idnumber
FROM mytable

which returns text data in this format:

idnumber
---------------------
148-ABC_C123_Q456
148-EXC_C112_S345
594-AZA_C4566_M5566
999-QWE_C9864_Q1234
1000-AAA_C434_Q454
1001-BAB_C234_Q6542
2734-BQW_C234_Q6577
etc

My problem is to find the best way to SELECT the numbers preceding the first dash ‘-‘ character. Currently all my data only ever has three digits preceding the ‘-‘ character, so using:

SELECT LEFT(idnumber, 3)

is working. But my data source is getting close to 999, so I need to find a way to select idnumber values that are longer than 3 characters.

This has always worked in the past:

SELECT
    CASE LEFT(idnumber,3)
        WHEN '148' THEN 'You selected 148'
        WHEN '594' THEN 'You selected 594'
        WHEN '999' THEN 'You selected 148'
    END
FROM 
    mytable

What can I use instead of LEFT(idnumber, 3) that will accommodate the future 4-digit idnumber values?

I tried CHARINDEX(), but that just returns NULL instead of the correct 3- or 4-digit idnumber:

SELECT
    CASE LEFT(idnumber, CHARINDEX('-', idnumber, -1))
        WHEN '148' THEN 'You selected 148'
        WHEN '594' THEN 'You selected 594'
        WHEN '999' THEN 'You selected 148'
        WHEN '1245' THEN 'You selected 1245'
        WHEN '1077' THEN 'You selected 1077'
    END
FROM 
    mytable

Am I using CHARINDEX() incorrectly? Or what are my alternatives?