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?

score 2 · Answer 1 · edited Jul 26 '13 at 05:55

2

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

edited Jul 26 '13 at 05:55

bummi

27,123
14
62
101

answered Jul 26 '13 at 05:32

Preet Sangha

64,563
18
145
216

score 1 · Answer 2 · answered Jul 26 '13 at 05:30

1

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

answered Jul 26 '13 at 05:30

Adriaan Stander

162,879
31
289
284

Suppose the name Duthja - AEPD - 700358492 the above query will give the entire thing – user1400915 Jul 26 '13 at 06:37
Did you try it? Have a look at this example. It seems to work. http://sqlfiddle.com/#!6/d41d8/5881 – Adriaan Stander Jul 26 '13 at 06:49
Yes I tried Duthja - AEPD - 700358492 will remain Duthja - AEPD - 700358492 – user1400915 Jul 26 '13 at 10:07

Amit Singh · Answer 3 · 2013-07-26T05:38:52.317

1

  SELECT TOP 100
             (CASE 
    WHEN CHARINDEX('-',PersonNameID )>0 AND 
          iSNUMERIC( rIGHT(PersonNameID ,CHARINDEX('-',PersonNameID )))=1
        THEN 
        lEFT(PersonNameID ,CHARINDEX('-',PersonNameID)-1)
       ELSE  
        PersonNameID
         END
        )  AS New_PersonNameID
            FROM Person

SQL FIDDLE DEMO

edited Jul 26 '13 at 05:38

answered Jul 26 '13 at 05:31

Amit Singh

8,039
20
29

score 1 · Answer 4 · edited Jul 26 '13 at 11:36

1

Any numeric after '-' will be deleted. You can play with the patindex pattern to suit your need.

SELECT 
    CASE WHEN PATINDEX('%-_[0-9]%',PersonNameID)>0 THEN LEFT(PersonNameID,CHARINDEX('-',PersonNameID)-1)
         ELSE PersonNameID
    END 
    AS New_PersonNameID
FROM Person

edited Jul 26 '13 at 11:36

Bogdan Sahlean

19,233
3
42
57

answered Jul 26 '13 at 07:03

Tman

61
1
1

score 0 · Accepted Answer · edited Dec 27 '19 at 06:42

I have found the solution to the above situation...

DECLARE @Person TABLE(
Person_NAME_Original VARCHAR(500),
Person_NAME_Modified VARCHAR(500)
)           

INSERT INTO @Person (Person_NAME_Original,Person_NAME_Modified)

SELECT top 1000 PersonNameID,
       CASE WHEN CHARINDEX('-',PersonNameID) = 0
                 THEN PersonNameID
            WHEN iSNUMERIC( rIGHT(PersonNameID ,CHARINDEX('-',REVERSE(PersonNameID))-1))=1
                 THEN  LEFT(PersonNameID, len(PersonNameID) - CHARINDEX('-',REVERSE(PersonNameID) )-1)
            ELSE PersonNameID
       END AS New_PersonNameID
from Person                                

select * from @Person

Substring in sql server on numbers

5 Answers5

SQL Fiddle DEMO