smart replace (re-phrase) text in SQL-CodePudding

I have a string in SQL with the following structure:

@number <logical> @number <logical> ....

ex:

@SQL='((@1 or @2) and (@10 or @21))'

I would like to update the string to be:

@SQL='((con_1=1 or con_2=1) and (con_10=1 or con_21=1))'

meaning remove the '@' and replace it with 'con_', leave the number (1 digit or more) as is and add '=1' after the digit.

Any idea how to do it?

Please avoid composing function or procedure for that.
you may use patindex, stuff or any other built in function for that.

CodePudding user response：

First split the string into records on the '@' char that starts the value needing processed.

STUFF can be used to insert the '=1' after the last digit of the value in each record. I think searching for the last number is harder than searching for the first. The reverse allows searching for the first instead of the last. Because the string is reversed, insert the reverse '1=' at the start of the reverse number. If there is no number, return the revered value as is.

Concatenate it all back with 'con_' instead of '@'.

declare @SQL varchar(200) = '  ((@1 or @2) and (@10 or @21)) @33 @55  ';

with split as (
    select value, REVERSE(value) as [rev], PATINDEX('%[0-9]%', REVERSE(value)) as [pat]
    FROM STRING_SPLIT(@SQL, '@')
), fixed as (
    select value, rev, pat, CASE WHEN pat= 0 THEN REVERSE(rev) ELSE REVERSE(STUFF(rev, pat, 0, '1=')) END as [fix] 
    FROM split
) select STRING_AGG(fix, 'con_') from fixed

Result: ((con_1=1 or con_2=1) and (con_10=1 or con_21=1)) con_33=1 con_55=1

It would be nice if Regex support was included in SQL server. I imagine to do so would require a careful implementation to avoid using all the server resources. A regex search on a millions of records or varchar(max) columns....

CodePudding user response：

Found this solution which uses SQL# (SQLsharp). SQL# is a .NET / CLR library that resides in a SQL Server 2005 (or newer) database and provides a suite of User-Defined Functions, Stored Procedures, User-Defined Aggregates, and User-Defined Types. My solution:

declare @SQL varchar(200) = '@1 OR(@2 AND @3 AND (@4 or @5 or @6) AND (@7 or @8))' SELECT SQL#.RegEx_Replace4k(@SQL, N'(@) (\d*)', N'CON_$2=1', -1, 1, N'')