Extract part of string based on nth instance of character

Question

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

Answer 1

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 <= 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

Answer 2

You don't need to use any functions for this. Just some string manipulation.

Sample data

create table tbl (path varchar(1000));
insert tbl 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');

Query:

select path, ltrim(rtrim(
 substring(
 replace(cast(path as nvarchar(max)),
 '\',
 replicate(cast(' ' as nvarchar(max)),10000)),
 60001, 10000)))
 from Tbl

Results:

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

Some notes:

1. String functions in SQL Server return the same type as their operands. You may notice that I cast both the path column and the space (' ') to nvarchar(max). Without these explicit casts, both would be truncated at 4000 characters - similar to the STUFF issue here.

2. The trick I am using makes use of the fact that spaces are not significant and can easily be removed using RTRIM+LTRIM. The spaces are made really large and form "walls" that effectively partition the data into "columns". The 7th column starts from 60001 (and a bit).

• MBielski: This method scales much better, but I have encountered problems with the large size of the columns. If you are using this method more than twice in a WHERE clause it starts negatively impacting performance, and the more you use, the worse it gets. I found that by shortening the values in REPLICATE and SUBSTRING by a factor of 100 (remove 2 0s) really helped the performance and still got me the desired results. Thanks very much for this solution.