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

Question

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.

miriamka · Accepted Answer · 2021-05-10 22:13:53Z

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

This is perfect thank you. It's a new level of learning for me (I've not done any proper XML querying like this before) but I've been able to sandwich the rest of the columns into it and get the split to work perfectly!

Javier · Accepted Answer · 2021-05-11 15:53:43Z

0

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

edited May 11, 2021 at 15:53

answered May 10, 2021 at 20:20

Javier

11 silver badge2 bronze badges

3 Comments

Yitzhak Khabinsky Over a year ago

(1) It is a flawed approach if you need to UPDATE the actual table before you can query it. (2) The proposed implementation will fail for the "...two forward slashes for the top level folder...".

Javier Over a year ago

Oups!, thanks. (1) You could use a temp table for transformation. (2) Added REPLACE for "two forwards slashes".

Javier Over a year ago

*(1) Or replace [Path] in SELECT query with the UPDATE value.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related