Regex in SQL Server Replace function-CodePudding

I have a variable with random text, let's say

DECLARE @sNumberFormat NVARCHAR(200) = 'rand{text.here,{999}also-Random9He8re'

I want to replace each 9 in {999} by [0-9]. So in this example I would like to get

'rand{text.here,[0-9][0-9][0-9]also-Random9He8re'

Problem is I never know how many 9 will be placed in brackets, so there can be {99} {9999} ..and go on. I also need to validate if there is any invalid character (not 9) then nothing should be replaced.

I have tried some combinations of REPLACE and PATINDEX functions, but I could not achieve that.

CodePudding user response：

Sans robust regex support, SQL Server's native functions do not give much help here. One approach, a bit hackish, would be to separate the input string into three components:

rand{text.here,
{999}
also-Random9He8re

Next, replace the 9 in the middle target substring with @, or some other character which you don't expect to appear anywhere else in your input string:

rand{text.here,
{@@@}
also-Random9He8re

Finally, replace the @ in the middle substring with [0-9] and then concatenate together to get the final result:

DECLARE @val NVARCHAR(200) = 'rand{text.here,{999}also-Random9He8re'

SELECT REPLACE(
           SUBSTRING(@val, 1, CHARINDEX('{9', @val) - 1)  
           REPLACE(SUBSTRING(@val,
                            CHARINDEX('{9', @val)   1,
                            CHARINDEX('9}', @val) - CHARINDEX('{9', @val)), '9', '@')  
           SUBSTRING(@val, CHARINDEX('9}', @val)   2, LEN(@val) - CHARINDEX('9}', @val)),
           '@', '[0-9]');

CodePudding user response：

So the lazy dev in me suggests this:

SELECT Replace(
         Replace(
           Replace(
             Replace(@input, '{9999}', '[0-9][0-9][0-9][0-9]')
           , '{999}', '[0-9][0-9][0-9]')
         , '{99}', '[0-9][0-9]')
       , '{9}', '[0-9]') AS result
;

You can keep extending as long as you like to perform your (one off?) replacements.

Quick. Simple. Extensible. Hacky.

Sometimes lazy is good enough.

CodePudding user response：

This could be done with CTE series. It works with an arbitrary number of "9" values in square brackets.

Declare @str varchar(max) = 'rand{text.here,{999}also-Random9He8re';

With A As 
(Select 1 As Pos
Union All 
Select Pos 1 As Pos From A Where Pos < LEN(@str)
),
B As (
Select STRING_AGG(Case When Chr Like '[{9}]' Then Chr Else ' ' End, '') As Chr 
From A Cross Apply (Select SUBSTRING(@str,A.Pos,1 )) As T(chr)
),
C As (
Select [value] As pattern, 
       REPLACE(REPLACE(REPLACE([value], '9', '[0-9]'),'{',''),'}','') As replacement, 
       ROW_NUMBER() Over (ORDER BY (SELECT NULL)) As Num, 
       COUNT(*) OVER (ORDER BY (SELECT NULL)) As Cnt
From B Cross Apply STRING_SPLIT(Chr,' ')
Where [value] Like '{%}' And [value] Like '%9%'
),
D As (
Select @str As Result, 1 As Num
Union All 
select REPLACE(Result, C.pattern, C.replacement) As Res , D.Num 1 As Num
From D Inner Join C On (D.Num=C.Num)
Where D.Num<=C.Cnt)
Select Top 1 Result
From D
Order by Num Desc

A - Getting a list of character positions in text
B - Getting text with spaces instead of characters other than '9','{','}'
C- Getting patterns and corresponding replacement values
D - Getting the result using REPLACEMENT function