Mssql subquery inside a substring
Use the STUFF(...) T-SQL function to strip the last character off.  First REVERSE the string from the query results, then use STUFF, then REVERSE the result again.
Like this:
SELECT REVERSE(STUFF(REVERSE('this is my string'), 1, 1, ''))

The result looks like:




Results




this is my strin




Here's the Microsoft Docs pages for STUFF and REVERSE.
Actually, looking at your query more closely, you can refine it slightly to eliminate the use of the REVERSE function.  Take the example below, where I'm concatenating the names of all the columns in the database with a semi-colon separator.
SELECT STUFF((
    SELECT N'; ' + c.name
    FROM sys.columns c
    FOR XML PATH (N'')
    ), 1, 2, N'');

I add the semi-colon at the start of the string, then simply use STUFF to replace the first 2 characters with &quot;nothing&quot;.
So the results look like:




Results




Column1; Column2; Column3




Newer versions of SQL Server (2017+) support the use of the STRING_AGG aggregate which can significantly simplify this kind of query.  To get the same output as my query above, using STRING_AGG, you'd write it like this:
SELECT STRING_AGG(CONVERT(nvarchar(max), c.name), N'; ')
FROM sys.columns c;

There is CONVERT(nvarchar(max), ...) wrapped around the c.name column to coerce the STRING_AGG function into using a string of sufficient length to contain all the column names in my database.  If you leave that out, SQL Server reports an error:

Msg 9829, Level 16, State 1, Line 10
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.