Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
share|improve this question
    
I am trying to get server name. –  user761786 Apr 25 '13 at 17:52

2 Answers 2

up vote 3 down vote accepted

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
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. sqlperformance.com/2012/07/t-sql-queries/split-strings –  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 (
'\\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).

share|improve this answer

Your Answer

 
discard

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.