How to Remove a second occurrence of a Symbols in a string using SQL Server?

Question

Input:

ROUND((#X# + #Y#) / #Z#,2 )

Output I'm looking for:

ROUND((@X + @Y) / @Z,2 )

Answer 1

Please try the following solution.

It is using XML and XQuery.

We are tokenizing the input string, and adding at sign (@) symbol for a predefined sequence of characters: X, Y, and Z.

SQL

DECLARE @round VARCHAR(MAX) = 'ROUND((#X# + #Y#) / #Z#,2 )'
   , @separator CHAR(1) = '#'
   , @atsign CHAR(1) = '@';

SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(@round, @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML).query('
        for $x in /root/r/text()
        return if($x=("X","Y","Z")) then concat(sql:variable("@atsign"), $x)
        else string($x)
').value('text()[1]','VARCHAR(MAX)');

Output

ROUND(( @X  +  @Y ) /  @Z ,2 )