Splitting column value by '\' in Sql server

Question

I have some data in FileFullPath Column

Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\Oracle\2022-05-04\MSudaitemlov_20220503
Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\OracleABC\2022-05-04\FDERDMSudaitemlov_20220503
Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\OCSBAGF\2022-05-04\AASSSMSudaitemlov_20220503

The part I wanted is:

Oracle
OracleABC
OCSBAGF

The letters are dynamic so, I couldn't apply Left,Right function since the length is different. I tried to split it using '\' by using STRING_SPLIT() but it's saying:

Msg 195, Level 15, State 10, Line 18
'string_split' is not a recognized built-in function name.

Answer 1

You should be able to use STRING_SPLIT() in SQL Server 2016, except in two scenarios:

1. If you're not calling the function correctly - many people try to call it like a scalar function (SELECT STRING_SPLIT(...) instead of a table-valued function (SELECT * FROM STRING_SPLIT(...). It returns a table, so you must treat it like a table.
2. If your database's compatibility level is lower than 130. This is called out at the very top of the documentation, and I've given several workarounds in this tip in cases where you can't change compat level.

But STRING_SPLIT() won't solve this problem anyway...

...because the output order is not guaranteed, so you could never reliably determine which element is 3rd from last.

Borrowing shamelessly from my work in this article, you can create the following simple function:

CREATE FUNCTION dbo.SplitOrdered_JSON
(
  @List      nvarchar(4000),
  @Delimiter nvarchar(255)
)
RETURNS table WITH SCHEMABINDING
AS
  RETURN
  (
    SELECT [key], value FROM OPENJSON
    (
      CONCAT
      (
        N'["',
        REPLACE(STRING_ESCAPE(@List, 'JSON'), 
          @Delimiter, N'","'),
        N'"]')
      ) AS x
    );

Then if you're after the 3rd-last element in the string, you can just reverse before parsing, and then reverse again after parsing. e.g.

CREATE TABLE #f(ID int, FullFilePath nvarchar(4000));

INSERT #f VALUES
(1,N'Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\Oracle\2022-05-04\MSudaitemlov_20220503'),
(2,N'Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\OracleABC\2022-05-04\FDERDMSudaitemlov_20220503'),
(3,N'Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\OCSBAGF\2022-05-04\AASSSMSudaitemlov_20220503');

DECLARE @ElementOfInterest int = 3;

SELECT REVERSE(value) 
  FROM #f CROSS APPLY dbo.SplitOrdered_JSON(REVERSE(FullFilePath), N'\')
  WHERE [key] = @ElementOfInterest - 1;

• Example db<>fiddle

Answer 2

Here is another solution for a complete coverage.

It will work starting from SQL Server 2012 onwards.

It is using XML and XQuery for tokenization. No need in any User-Defined-Function (UDF).

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, FullFilePath nvarchar(4000));
INSERT INTO @tbl (FullFilePath) VALUES
(N'Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\Oracle\2022-05-04\MSudaitemlov_20220503'),
(N'Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\OracleABC\2022-05-04\FDERDMSudaitemlov_20220503'),
(N'Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\OCSBAGF\2022-05-04\AASSSMSudaitemlov_20220503');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '\'
    , @token int = 8;

SELECT t.* 
    , c.value('(/root/r[sql:variable("@token")]/text())[1]', 'NVARCHAR(20)')
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
      REPLACE(FullFilePath, @separator, ']]></r><r><![CDATA[') + 
      ']]></r></root>' AS XML)) AS t1(c);

Output

+----+--------------------------------------------------------------------------------------------------------+------------------+
| ID |                                              FullFilePath                                              | (No column name) |
+----+--------------------------------------------------------------------------------------------------------+------------------+
|  1 | Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\Oracle\2022-05-04\MSudaitemlov_20220503         | Oracle           |
|  2 | Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\OracleABC\2022-05-04\FDERDMSudaitemlov_20220503 | OracleABC        |
|  3 | Y:\dfs-dc-01\Split\Retail\Kroger\Kroger\FTP-FromClient\OCSBAGF\2022-05-04\AASSSMSudaitemlov_20220503   | OCSBAGF          |
+----+--------------------------------------------------------------------------------------------------------+------------------+