Easier way to reverse the order of a canonical name

Question

I'm wondering if there is an easier way to reverse an Active Directory canonical name in SQL. I found a way to do this, but it only works with a set number of OU's, and I'd like it to work with any number of OU's.

For example: I'd like to take a row with a Canonical Name of "domain.org/company/users/department/john smith" and reverse the string order so it displays "John Smith/department/users/company/domain.org"

To do this I used a few different XML methods I found from other posts. The code below converts the string to XML and counts the number of nodes in the value. It then uses a case statement to concatenate the string back together in reverse order.

As you can see in the case statement, I'd have to hand jam every possible number of nodes into the case statement for this to work currently. If possible I'd like to take the node count and loop backwards through the nodes counting down, but I'm not sure how to achieve this with SQL or if there is a better way to accomplish what i'm trying to do.

  SELECT *
        ,CASE 
           WHEN NodeCount = 4 
             THEN CONCAT('"',MyXML.value('/root[1]/i[4]','varchar(100)'),'/',MyXML.value('/root[1]/i[3]','varchar(100)'),'/',MyXML.value('/root[1]/i[2]','varchar(100)'),'/',MyXML.value('/root[1]/i[1]','varchar(100)'),'"')
           WHEN NodeCount = 5 
             THEN CONCAT('"',MyXML.value('/root[1]/i[5]','varchar(100)'),'/',MyXML.value('/root[1]/i[4]','varchar(100)'),'/',MyXML.value('/root[1]/i[3]','varchar(100)'),'/',MyXML.value('/root[1]/i[2]','varchar(100)'),'/',MyXML.value('/root[1]/i[1]','varchar(100)'),'"')
           WHEN NodeCount = 6 
             THEN CONCAT('"',MyXML.value('/root[1]/i[6]','varchar(100)'),MyXML.value('/root[1]/i[5]','varchar(100)'),'/',MyXML.value('/root[1]/i[4]','varchar(100)'),'/',MyXML.value('/root[1]/i[3]','varchar(100)'),'/',MyXML.value('/root[1]/i[2]','varchar(100)'),'/',MyXML.value('/root[1]/i[1]','varchar(100)'),'"')
        END AS ReversedPath
  FROM (
    SELECT CanonicalName
           ,(CONVERT(XML, '<root><i>' + REPLACE(CanonicalName , '/', '</i><i>') + '</i></root>').query('.')) MyXML
           ,(CONVERT(XML, '<root><i>' + REPLACE(CanonicalName , '/', '</i><i>') + '</i></root>').value('count(/root/i)', 'INT')) NodeCount 
    FROM  dbo.[GetActiveDirectoryUsers] 
    WHERE AccountIsEnabled = 'True') T

If anyone has some ideas on other ways to approach this problem it would be appreciated.

For Reference: I used these scripts to populate the SQL tables with my AD user information. https://www.sqlservercentral.com/articles/powershell-to-get-active-directory-users-and-groups-into-sql

EDIT: Went with Kevin's suggestion, created a scaler-valued function with the code below.

ALTER FUNCTION [dbo].[fnReverseString] (@string varchar(500),@splitChar varchar(1)) 


RETURNS varchar(500)
AS
BEGIN
DECLARE @rev varchar(500);

with cte as 
(
   select 1 pos_from, charindex(@splitChar, @string) + 1 pos_to
      union all
   select pos_to, charindex(@splitChar, @string + @splitChar, pos_to) + 1
      from cte
   where pos_to <= len(@string)
)

select @rev = coalesce( @rev + @splitChar, '') + substring(@string, pos_from, pos_to - pos_from - 1) 
from cte
order by pos_to desc

return @rev
END

Now I can call this from my query and create the requested output for the vendor. Thanks again!

WITH CTE1 AS (
SELECT dbo.fnReverseString(CanonicalName,'/') ReversedPath
FROM dbo.[GetActiveDirectoryUsers] 
WHERE AccountIsEnabled = 'True'
)

SELECT '[' + STUFF((SELECT ',' + CONCAT('"',ReversedPath,'"')  AS [text()] FROM CTE1 FOR XML PATH ('')), 1, 1, '') + ']' FinalResult

Output

["John Smith/department/users/company/domain.org","Jane Smith/department/users/company/domain.org"]

You could do it with either a loop or cte, I think both of those are an easier/better way. — Kevin, Oct 23 '20 at 18:54

score 1 · Accepted Answer · answered Oct 23 '20 at 18:57

Here is how you could do it with a CTE (modified from this post Reverse Order of Words)

declare @domain varchar(500), @rev varchar(500)

set @domain = 'domain.org/company/users/department/john smith'


;with cte as 
(
   select 1 pos_from, charindex('/', @domain) + 1 pos_to
      union all
   select pos_to, charindex('/', @domain + '/', pos_to) + 1
      from cte
   where pos_to <= len(@domain)
)
select @rev = coalesce( @rev + '/', '') +substring(@domain, pos_from, pos_to - pos_from - 1) 
from cte
order by pos_to desc

select @rev

Thanks Kevin, that's much cleaner. I appreciate the help! – BurtonFive Oct 24 '20 at 16:13 — BurtonFive, Oct 24 '20 at 16:13

Easier way to reverse the order of a canonical name

1 Answers1