Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to find a way to extract part of the strings below. I need everything between the 6th and 7th \ character.


Desired results:

share|improve this question
I am trying to get server name. – user761786 Apr 25 '13 at 17:52
up vote 4 down vote accepted

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

CREATE FUNCTION dbo.SplitStringsOrdered
    @List       NVARCHAR(2000),
    @Delimiter  NVARCHAR(32)
      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));


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


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
share|improve this answer
Good article by @Aaron - Split strings the right way – or the next best way. It highlights various methods to split the string with performance comparison.Excellent stuff. – Kin Apr 25 '13 at 16:37
@Kin thanks, but that article doesn't really address how to get the nth element in a string based on a delimiter. – Aaron Bertrand Apr 25 '13 at 17:05
thought will be useful to know when dealing with splitting the strings in general as you have explained in a very detailed and easy way. – Kin Apr 25 '13 at 17:10
There's something wrong (minor) with the answer - running it verbatim returns Wednesday etc for s.rn=6, not ServerA etc as the result shows. – 孔夫子 Apr 26 '13 at 3:40
孔夫子 I was originally missing the leading \ – Aaron Bertrand Apr 26 '13 at 14:41

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 (


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


|                                                                           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).

share|improve this answer
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. – MBielski Aug 13 '15 at 17:45

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.