Pulling substring data between underscores

Question

I have a column called OriginalFileName in table [Import]. The data looks like below. The format is ID_LastName_FirstName_etc. I am trying to write a statement that will pull the ID, LastName, and FirstName into their own columns.

2900411_DOE_JOHN_1013273960_SMITH_012018-122018_TEST_1023194929605635701.pdf 

I was able to get the ID and last name using this:

1. ID:

SELECT LEFT(OriginalFileName, charindex('_', OriginalFileName) -1) from IMPORT

2. LASTNAME (underscore value keeps disappearing when I copy and paste so I have replaced the underscore symbol with the actual word):

SELECT SubString(OriginalFileName, (CHARINDEX('underscore', OriginalFileName, 0) + 1),(CharIndex('underscore', RIGHT(OriginalFileName, (LEN(OriginalFileName) - (CharIndex('underscore', OriginalFileName, 0)))), 0) - 1)) from IMPORT

3. FIRSTNAME:

?????????????????????????????

However, I am having a lot of trouble getting the first name out. Please assist. The length of the data separated by underscores will vary. I got those two other statements from other posts but was unable to figure out how to modify in order to get the first name value.

Answer 1

This is one method, assuming that surname and forename are the 2nd and 3rd parts respectively, and there are more parts afterwards:

SELECT V.Filename,
       SUBSTRING(V.Filename, SN.CI+1, FNs.CI - SN.CI -1 ) AS Surname,
       SUBSTRING(V.Filename, FNs.CI+1, FNe.CI - FNs.CI -1 ) AS Forename
FROM (VALUES('2900411_DOE_JOHN_1013273960_SMITH_012018-122018_TEST_1023194929605635701.pdf')) V(Filename)
     CROSS APPLY (VALUES(CHARINDEX('_',V.Filename)))SN(CI)
     CROSS APPLY (VALUES(CHARINDEX('_',V.Filename,SN.CI+1)))FNs(CI)
     CROSS APPLY (VALUES(CHARINDEX('_',V.Filename,FNs.CI+1)))FNe(CI);

Answer 2

Just another option.

Not clear if the positions are fixed to Pos2 and Pos3

Example

Declare @YourTable table (ID int,OrginalFileName varchar(500))
Insert Into @YourTable values
(1,'2900411_DOE_JOHN_1013273960_SMITH_012018-122018_TEST_1023194929605635701.pdf')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Pos2 = xDim.value('/x[2]','varchar(max)')
                      ,Pos3 = xDim.value('/x[3]','varchar(max)')
                From  (Select Cast('<x>' + replace(OrginalFileName,'_','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

Answer 3

Another option to consider, doing this with a series of update statements to parse column by column. Less elegant than @Larnu's, but maybe more flexible.

DECLARE @str VARCHAR(1000) = '2900411_DOE_JOHN_1013273960_SMITH_012018-122018_TEST_1023194929605635701.pdf'
DROP TABLE #results
CREATE TABLE #results (origstring VARCHAR(MAX), col1 INT, lnm VARCHAR(10), fnm VARCHAR(10), col4 VARCHAR(20), col5 VARCHAR(20), col6 VARCHAR(20), col7 VARCHAR(20), col8 VARCHAR(20))

INSERT INTO #results (origstring)
SELECT @str

UPDATE #results
SET col1 = LEFT(origstring, CHARINDEX('_', origstring) - 1)
, origstring = right(origstring, len(origstring) - CHARINDEX('_', origstring))

UPDATE #results
SET lnm = LEFT(origstring, CHARINDEX('_', origstring) - 1)
, origstring = right(origstring, len(origstring) - CHARINDEX('_', origstring))

UPDATE #results
SET fnm = LEFT(origstring, CHARINDEX('_', origstring) - 1)
, origstring = right(origstring, len(origstring) - CHARINDEX('_', origstring))

UPDATE #results
SET col4 = LEFT(origstring, CHARINDEX('_', origstring) - 1)
, origstring = right(origstring, len(origstring) - CHARINDEX('_', origstring))

--etc, for the rest of  the columns

Each step basically cuts the first part off of the remaining original string and inserts it into a column.