Substring from a String in SQL Server after a symbol to the end of the field if the symbol exists

user_5311964 0
Viewed 110

I've queried the Catalog in the ReportServer database to get the list of folders that exist in the report server. So I have results that look like this:

[Path]
/Admin
/Compliance/Self Serve/Dublin
/Directors/Daily Reports

So there could be a single forward slash for a top level folder, two forward slashes for the top level folder and one level down and then 3 or more for even deeper folder levels.

I need to break this into two parts. The first column should just be the top level folder name without the starting forward slash and the second part should be the rest of the folder structure from the next level down, e.g.

[Folder]         [Subfolders]
Admin            
Compliance       Self Serve/Dublin
Directors        Daily Reports

There are lots of methods of getting strings before and after symbols that do part of what I need, but the issue of the top level folders is stumping me, where there is only one forward slash.

All suggestions desperately received.

uisplitviewcontroller sql-server string
2 Answers

Please try the following solution.

It is based on the idea of tokenization.

  • First token (XPath predicate [1]) would be the root folder.
  • The rest of the tokens (XPath predicate [position() gt 1]) are the subfolders.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, [path] NVARCHAR(MAX));
INSERT INTO @tbl (path) VALUES
('/Admin'),
('/Compliance/Self Serve/Dublin'),
('/Directors/Daily Reports'),
('//Directors/Daily Reports/Monday/Tuesday');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '/';

WITH rs AS
(
    SELECT * 
        , TRY_CAST('<root><x><![CDATA[' + 
          REPLACE([path], @separator, ']]></x><x><![CDATA[') + 
          ']]></x></root>' AS XML) AS xmldata
    FROM @tbl
)
SELECT ID, [Path]
    , xmldata.value('(/root/x[text()][1]/text())[1]', 'NVARCHAR(100)') AS [Folder] 
    , REPLACE(xmldata.query('
        for $x in /root/x[text()][position() gt 1]
        return if ($x is (/root/x[position() = last()])[1]) then data($x)
            else concat($x, sql:variable("@separator"))
    ').value('text()[1]', 'NVARCHAR(MAX)'), '/ ', '/') AS [Subfolders]
FROM rs;

Output

+----+------------------------------------------+------------+------------------------------+
| ID |                   Path                   |   Folder   |          Subfolders          |
+----+------------------------------------------+------------+------------------------------+
|  1 | /Admin                                   | Admin      | NULL                         |
|  2 | /Compliance/Self Serve/Dublin            | Compliance | Self Serve/Dublin            |
|  3 | /Directors/Daily Reports                 | Directors  | Daily Reports                |
|  4 | //Directors/Daily Reports/Monday/Tuesday | Directors  | Daily Reports/Monday/Tuesday |
+----+------------------------------------------+------------+------------------------------+
user_35156130

I would guarantee the end of the directory name with a "/" char, in this way you can opt for simple solutions (like SUBSTRING + CHARINDEX).

Example:

DECLARE @SEP VARCHAR(1) = '/'
DECLARE @CATALOG TABLE ( [Path] VARCHAR(200) )

INSERT INTO @CATALOG ([Path]) 
VALUES  ('/Admin'),
        ('/Compliance/Self Serve/Dublin'),
        ('/Directors/Daily Reports'),
        ('//Directors/Daily Reports/Monday/Tuesday')

UPDATE @CATALOG SET [Path] = REPLACE([Path], '//', '/') + @SEP -- At least one "/" at the end of the directory.

SELECT          
    SUBSTRING([Path], 2, CHARINDEX(@SEP, [Path], 2) -2)         AS Folder,
    SUBSTRING([Path], CHARINDEX(@SEP, [Path], 2) + 1, LEN([Path]) - IIF(LEN([Path]) > CHARINDEX(@SEP, [Path], 2) +1, CHARINDEX(@SEP, [Path], 2) +1, CHARINDEX(@SEP, [Path], 2) ) )  AS Subfolders
FROM @CATALOG
user_158897600
Related