Split String Function

Question

I have a question regarding this Split String function Aaron Bertrand recommends here.

With it you can extract information between two slashes. Now if I want to extract not only one piece of info like the servername, but also a second piece like the day of the week. How would I have to rewrite the code in order to get that info in one result set?

Here's a sample of my data

E:\BaseData\RUK\HPP_Conversion_Detail\hpp_conversion_detail_report_2015_11_02.csv
E:\BaseData\RUK\Manual_Review\manual_review_report_2015_11_01.csv
E:\BaseData\RUK\Disputes\dispute_report_2015_11_01.csv
E:\BaseData\RSE\HPP_Conversion_Detail\hpp_conversion_detail_report_2015_11_02.csv
E:\BaseData\RSE\Manual_Review\manual_review_report_2015_11_01.csv
E:\BaseData\RSE\Disputes\dispute_report_2015_11_01.csv

I'm interested in everything between the second and third slash and the third and fourth slash. What Geoff suggested is a good idea. I'm then getting the results in rows. Is it possible to get the results back in additional columns?

Thanks!

Answer 1

Using what Aaron Bertrand recommends here.

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

And your example data

CREATE TABLE #x
(filepath NVARCHAR(2000));

INSERT #x VALUES
('E:\BaseData\RUK\HPP_Conversion_Detail\hpp_conversion_detail_report_2015_11_02.csv'),
('E:\BaseData\RUK\Manual_Review\manual_review_report_2015_11_01.csv'),
('E:\BaseData\RUK\Disputes\dispute_report_2015_11_01.csv'),
('E:\BaseData\RSE\HPP_Conversion_Detail\hpp_conversion_detail_report_2015_11_02.csv'),
('E:\BaseData\RSE\Manual_Review\manual_review_report_2015_11_01.csv'),
('E:\BaseData\RSE\Disputes\dispute_report_2015_11_01.csv')

Then you can run something like

SELECT * FROM  #x x
CROSS   APPLY dbo.SplitStringsOrdered(x.filepath, '\') sso
WHERE sso.rn = 3

and get out the third part.

There are several ways to get at multiple parts. You could just drop the WHERE sso.rn = 3 from the end but the data becomes spread over lots of rows which might not work for you.

Something like this might work for you using sub-queries.

SELECT *
, Part3 = (SELECT sso.Item FROM dbo.SplitStringsOrdered(x.filepath, '\') sso
WHERE sso.rn = 3) 
, Part5 = (SELECT sso.Item FROM dbo.SplitStringsOrdered(x.filepath, '\') sso
WHERE sso.rn = 5) 
FROM  #x x

WARNING above the code highlighting mistakenly thinks that the \ escapes the single quote following it which is not in fact the case in SSMS/SQL-server unless you ask for that behavior.

If you want everything a hack using MIN Pivot might be the best for you.

;
WITH    splits
          AS (
               SELECT   x.filepath
                      , sso.rn
                      , sso.Item
               FROM     #x x
               CROSS APPLY dbo.SplitStringsOrdered(x.filepath, '\') sso
             )
             SELECT * 
             FROM
             (  SELECT  splits.filepath
          , splits.rn
          , splits.Item
    FROM    splits   )  AS src
    PIVOT ( MIN(Item)
    FOR rn IN ([1],[2],[3],[4],[5])
           )   AS pvt

But that ends up looking a bit crazy, isn't in fact as flexible or as useful as it might appear, and the aggregate MIN is ugly.

So you could go for a mix of the two without the pivot hack and with more sub query.

;WITH   splits
          AS (
               SELECT   x.filepath
                      , sso.rn
                      , sso.Item
               FROM     #x x
               CROSS APPLY dbo.SplitStringsOrdered(x.filepath, '\') sso
             )
    SELECT  x.filepath
          , (
              SELECT s.Item FROM splits s WHERE s.rn = 2 AND s.filepath = x.filepath
            )
          , (
              SELECT s.Item FROM splits s WHERE s.rn = 3 AND s.filepath = x.filepath
            )
          , (
              SELECT    s.Item
              FROM      splits s
              WHERE     s.rn = (
                                 SELECT MAX (splits.rn) FROM splits
                               )
                        AND s.filepath = x.filepath
            )
    FROM    #x x

Answer 2

Try doing the regular split, getting strings that look like 20151002SERVER1. Then you can just refer to CONVERT(date,LEFT(Item,8),112) and STUFF(Item,1,8,'') as your date and server name.