TSQL: Parsing strings with various characters

Question

I have a table with a file name column where various vendors named files differently. So there is a filename with a last, first, middle name in the file with various characters separating the name. Some have a comma + space, comma with no space, spaces between words, no spaces between words, an underscore, two underscores etc.

What are some good ways this be extracted to the desired result? (It's a one-time data conversion, doesn't have to be pretty.)

What I've tried is in the sample code below, using various substring/charindex combinations

Filename sample: (note the commas,spaces,no-spaces,underscores, double-underscores)

Desired Results:

Sample Code / Test Data (in a temp table)

   IF OBJECT_ID('tempdb..#dob') IS NOT NULL DROP TABLE #dob

   CREATE TABLE #dob (
    FILENAME VARCHAR(MAX)
   ,StudentID INT
   ,FullName VARCHAR(500)
   ,LastName VARCHAR(500)
   ,FirstName VARCHAR(500)
   ,MiddleName VARCHAR(500)
   )

   INSERT INTO #dob
   ( FILENAME  )
   VALUES
    ('Last, First, Middle_DOB ID.pdf')
   ,('Denver, John C 11_23_1980_123456.pdf')
   ,('Denver John_11-23-1980, 1234567.pdf')
   ,('Denver,John,Clifford_ 01_22_1980_123456.pdf')
   ,('Denver, John, 11-23-1980, 1234567.pdf')
   ,('Denver, John__01_22_1980_123456.pdf')

   --This is what I tried.

   SELECT FILENAME
     ,fullname
     ,LastName
     ,FirstName
     ,MiddleName
     ,SUBSTRING(FileName,1, CHARINDEX(' ', FileName, (charindex(' ', FileName, 1))+2)) AS test1
     ,SUBSTRING(FileName,1, CHARINDEX('_', FileName, (charindex(' ', FileName, 1))+2)) AS test2
     ,SUBSTRING(FileName,1, CHARINDEX(',', FileName, (charindex(', ', FileName, 1))+1)) AS test3
     ,SUBSTRING(FileName,1, CHARINDEX(' ', FileName, (charindex('__', FileName, 1))+2)) AS test4
     ,SUBSTRING(FileName,1, CHARINDEX('__', FileName, (charindex(' ', FileName, 1))+2)) AS test5


   FROM #dob

John Cappelletti · Accepted Answer

This is a slippery slope, but if your real data is close to the sample, consider the following.

Example

   SELECT FILENAME
         ,LastName   = Pos1
         ,FirstName  = Pos2
         ,MiddleName = case when try_convert(int,left(Pos3,1)) is null then Pos3 else '' end
   FROM #dob A
   Cross Apply ( values ( replace(
                          replace(
                          replace(
                          replace(FileName,', ',',')
                          ,' ,',',')
                          ,' ',',')
                          ,'_',',')
                        )
               ) B(CleanString)
   Cross Apply [dbo].[tvf-Str-Parse-Row](CleanString,',') C

Returns

FILENAME                                      LastName  FirstName   MiddleName
Last, First, Middle_DOB ID.pdf                Last      First       Middle
Denver, John C 11_23_1980_123456.pdf          Denver    John        C
Denver John_11-23-1980, 1234567.pdf           Denver    John    
Denver,John,Clifford_ 01_22_1980_123456.pdf   Denver    John        Clifford
Denver, John, 11-23-1980, 1234567.pdf         Denver    John    
Denver, John__01_22_1980_123456.pdf           Denver    John

The TVF if interested

CREATE FUNCTION [dbo].[tvf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
          ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
          ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
          ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
          ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
          ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
          ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
    From  ( values (cast('' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','')+'' as xml))) as A(xDim)
)

TSQL: Parsing strings with various characters

Answers (2)

Related Questions