TSQL - Split delimited String into columns

Question

---

The Problem

I have a number of filename strings that I want to parse into columns using a tilda as delimiter. The strings take on the static format:

• Filepath example C:\My Documents\PDF
• Surname example Walker
• First Name example Thomas
• Birth Date example 19991226
• Document Created Datetime example 20180416150322
• Document Extension example .pdf

So a full concatenated example would be something like:

C:\My Documents\PDF\Walker~Thomas~19991226~20180416150322.pdf

I want to ignore the file path and extension given in the string and only parse the following values into columns:

• Surname, First Name, Birth Date, Document Created Datetime

So something like:

SELECT Surname = --delimitedString[0]
       FirstName = --delimitedString[1]
       --etc.

---

What I have tried

I know that I have several tasks I would need to perform in order to split the string, first I would need to trim off the extension and file path so that I can return a string delimited by tildas (~).

This is problem one for me, however problem 2 is splitting the new delimted string itself i.e.

Walker~Thomas~19991226~20180416150322

Ive had a good read through this very comprehensive question and It seems (as im using SQL Server 2008R2) the only options are to use either a function with loops or recursive CTE's or attempt a very messy attempt using SUBSTRING() with charIndex().

Im aware that If I had access to SQL Server 2016 I could use string_split but unfortunately I cant upgrade.

I do have access to SSIS but im very new to it so decided to attempt the bulk of the work within a SQL statement

Answer 1

Here is a way without a splitter that shouldn't be too complicated...

declare @var table (filepath varchar(256))
insert into @var values
('C:\My Documents\PDF\Walker~Thomas~19991226~20180416150322.pdf')


;with string as(
select 
    x = right(filepath,charindex('\',reverse(filepath))-1) 
    from @var
)

select
    SurName= substring(x,1,charindex('~',x) - 1)
    ,FirstName = substring(x,charindex('~',x) + 1,charindex('~',x) - 1)
from string

Answer 2

I know you mentioned wanting to avoid the charindex() option if at all possible, but I worked it out in a hopefully semi-readable way. I find it somewhat easy to read complex functions like this when I space each parameter on a different line and use indent levels. It's not the most proper looking, but it helps with legibility:

with string as (select 'C:\My Documents\PDF\Walker~Thomas~19991226~20180416150322.pdf' as filepath)

select 
    substring(
        filepath,
        len(filepath)-charindex('\',reverse(filepath))+2,           --start location, after last '\'
        len(filepath)-                                              --length of path
            (len(filepath)-charindex('\',reverse(filepath))+2)-         --less characters up to last '\'
            (len(filepath)-charindex('.',filepath))                     --less file extention
        )
from string

Answer 3

Fritz already have a great start, my answer just add on top it

with string as (select 'C:\My Documents\PDF\Walker~Thomas~19991226~20180416150322.pdf' as filepath)
, newstr as (
select 
    REPLACE(substring(
        filepath,
        len(filepath)-charindex('\',reverse(filepath))+2,           --start location, after last '\'
        len(filepath)-                                              --length of path
            (len(filepath)-charindex('\',reverse(filepath))+2)-         --less characters up to last '\'
            (len(filepath)-charindex('.',filepath))                     --less file extention
        ) , '~', '.') as new_part
from string
)
SELECT
      PARSENAME(new_part,4) as Surname,
      PARSENAME(new_part,3) as [First Name],
      PARSENAME(new_part,2) as [Birth Date], 
      PARSENAME(new_part,1) as [Document Created Datetime]
FROM newstr