Getting Vales From Multiple Delimiters

I have a name field having multiple delimiters like: ----------------------------------- lastname ----------------------------------- John / Sam Saw - Dont Want To Share Name / Ki

Solution 1:

One possible approach is to use only one delimiter (\) and replace all other possible delimiters (/, -) with this one.

Table:

CREATE TABLE Data (LastName varchar(1000))
INSERT INTO Data (LastName)
VALUES
   ('John'),
   ('/ Sam Saw'),
   ('- Dont Want To Share Name'),
   ('/ Kim/ Ph  quu HO -Ceo / Mr ADi gilero'),
   ('(Tor Tim)'),
   ('(R)/ Nikki(R) / Phi / Hali'),
   ('/ Isi Anna'),
   ('\ Lorren Lush \ Ann')

Statement:

SELECT 
   REVERSE(LEFT(
      ReverseName, 
      CASE 
         WHEN CHARINDEX('\', ReverseName) = 0 THEN LEN(ReverseName) 
         ELSE CHARINDEX('\', ReverseName) - 1 
      END
   )) AS LastName
FROM (
   SELECT REVERSE(REPLACE(REPLACE(LastName, '/', '\'), '-', '\')) AS ReverseName 
   FROM Data
) t

Result:

-------------------------
LastName
-------------------------
John
 Sam Saw
 Dont Want To Share Name
 Mr ADi gilero
(Tor Tim)
 Hali
 Isi Anna
 Ann

Solution 2:

You can use SUBSTRING along with PATINDEX

SELECT SUBSTRING(PRE_LN,1,CASE WHEN G.POS!=0 THEN G.POS-1 
                          ELSE LEN(PRE_LN) END) AS LASTNAME
FROM (
    SELECT  SUBSTRING(LASTNAME,P1.POS, LEN(LASTNAME)-P1.POS+1) PRE_LN FROM TEST
    CROSS APPLY (VALUES(PATINDEX('%[A-Za-z]%',LASTNAME)))P1(POS)
    )SQ
CROSS APPLY (VALUES(PATINDEX('%[^A-Za-z ]%',PRE_LN)))G(POS)

Check Demo Here

This will give the output as follows.

This will give you first value.Since you had mentioned first value or last value