Extract part of string based on nth instance of character I am trying to find a way to extract part of the strings below. I need everything between the 6th and 7th \ character.

\\fileServerA\d$\LiteSpeed\Wednesday\ServerA\Tlog\DBA1_TLOG_20110504_0333.SLS
\\fileServerA\d$\LiteSpeed\Wednesday\ServerB\Tlog\model_TLOG_20120321_1038.SLS
\\fileServerA\d$\LiteSpeed\Saturday\ServerA\TLog\DBA_2_TLOG_20120811_1538.SLS
\\fileServerA\d$\LiteSpeed\Friday\ServerB\Tlog\DB3_TLOG_20120914_2330.BAK
\\fileServerA\d$\LiteSpeed\Wednesday\ServerC\Tlog\DB44_TLOG_20120815_1445.BAK


Desired results:

ServerA
ServerB
ServerA
ServerB
ServerC
Well, one way would be to use an ordered split function:

CREATE FUNCTION dbo.SplitStringsOrdered
(
    @List       NVARCHAR(2000),
    @Delimiter  NVARCHAR(32)
)
RETURNS TABLE
AS
    RETURN 
    (
      SELECT rn = ROW_NUMBER() OVER (ORDER BY Number), Item 
        FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
          CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
          FROM sys.all_objects) AS n(Number)
        WHERE Number &lt;= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
      ) AS y);


Sample usage:

DECLARE @x TABLE(filepath NVARCHAR(2000));

INSERT @x VALUES
('\\fileServerA\d$\LiteSpeed\Wednesday\ServerA\Tlog\DBA1_TLOG_20110504_0333.SLS'),
('\\fileServerA\d$\LiteSpeed\Wednesday\ServerB\Tlog\model_TLOG_20120321_1038.SLS'),
('\\fileServerA\d$\LiteSpeed\Saturday\ServerA\TLog\DBA_2_TLOG_20120811_1538.SLS'),
('\\fileServerA\d$\LiteSpeed\Friday\ServerB\Tlog\DB3_TLOG_20120914_2330.BAK'),
('\\fileServerA\d$\LiteSpeed\Wednesday\ServerC\Tlog\DB44_TLOG_20120815_1445.BAK');

SELECT x.filepath, s.Item FROM @x AS x
  CROSS APPLY dbo.SplitStringsOrdered(x.filepath, '\') AS s
  WHERE s.rn = 7;


Results:

filepath                                     Item
-------------------------------------------  -------
\\file...eed\Wednesday\ServerA\Tlog\DBA1...  ServerA
\\file...eed\Wednesday\ServerB\Tlog\mode...  ServerB
\\file...eed\Saturday\ServerA\TLog\DBA_2...  ServerA
\\file...eed\Friday\ServerB\Tlog\DB3_TLO...  ServerB
\\file...eed\Wednesday\ServerC\Tlog\DB44...  ServerC