CODEWITHSUNDEEP
sqlsql-serverselectcharindex
luisdev
luisdev

Reputation: 568

Using CHARINDEX() in T-SQL to select a part of a nvarchar value

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?

Upvotes: -1

Views: 205

Answers (1)

Mureinik
Mureinik

Reputation: 312146

You have the right idea, but have a mistake with your parenthesis. You're passing -1 as the third argument to charindex, which means the search will start from the -1st character of the string, which is meaningless, and just treated as the first character.

Fix the parenthesis so that you subtract 1 from the result of the charindex call and you should be fine:

LEFT(idnumber, CHARINDEX('-', idnumber) - 1)
-- Notice the ')' location -----------^

Upvotes: 4

Related Questions