Substring in sql server on numbers

Question

I have a column name called "PersonNameID"

which contains two values

ABCD-GHJI
ABHK-67891
HJKK-HJJJMH-8990

I have to extract only the first part of the "PersonNameID" which contains number after "-".Ideally my output should be

ABCD-GHJI
ABHK
HJKK-HJJJMH

but when I use following code :

SELECT TOP 100

     CONVERT(NVARCHAR(100),

     SUBSTRING(PersonNameID, 1,
          CASE
               WHEN CHARINDEX('-', PersonNameID) > 0 
                    THEN LEN(PersonNameID) - 
                         LEN(REVERSE(SUBSTRING(REVERSE(PersonNameID), 1, CHARINDEX('-', REVERSE(PersonNameID))))) 
                    ELSE LEN(PersonNameID)

          END
     )
     ) AS New_PersonNameID
FROM Person

I get the output as

ABCD
ABHK
HJKK

Any modifications to the above code to get the desired output?

Answer 1

Use pattern matching to find the numeric ones and then work out where the numeric

SELECT
    LEFT(PersonNameID,
            CASE WHEN PersonNameID LIKE '%[0-9]%' AND CHARINDEX('-', PersonNameID) > 0
            THEN 
                CHARINDEX('-', PersonNameID)-1 
            ELSE 
                LEN(PersonNameID) 
            END) AS NewPersonId
FROM 
    Person

Answer 2

How about something like

DECLARE @Person TABLE(
        PersonNameID VARCHAR(50)
)

INSERT INTO @Person VALUES ('ABCD-GHJI'),('ABHK-67891')

SELECT           top 100 
CASE
    WHEN CHARINDEX('-',PersonNameID ) = 0
        THEN PersonNameID
    WHEN ISNUMERIC(RIGHT(PersonNameID,LEN(PersonNameID) - CHARINDEX('-',PersonNameID ))) = 0
        THEN PersonNameID
    ELSE LEFT(PersonNameID, CHARINDEX('-',PersonNameID )-1)
END AS New_PersonNameID
from @Person

SQL Fiddle DEMO