Input:

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

Output I'm looking for:

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

CodePudding user response：

;WITH sampleData AS
(
    SELECT myArray = 'round(#A1#   #A2#,0) 123451'
    )

,cte2 as
    (
SELECT
     [myArray] 
    ,value as val
FROM [sampleData]
CROSS APPLY STRING_SPLIT([myArray],'#'))

,cet3 as(
select case when val like '%A%' or val like '%B%' then '@' val 
else val end as finalformula from cte2 as A),

cet4 as(select cet3.finalformula as formula from cet3)

SELECT STRING_AGG(cet4.formula,'') AS Result FROM cet4

CodePudding user response：

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, T, and Z.

SQL

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

DECLARE @xml_round XML = TRY_CAST('<root><r><![CDATA['   
      REPLACE(@round, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML);

SELECT @xml_round.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 )