Replace email inside varchar tsql

Question

I have an email address inside stringified JSON, in a column.

I need to replace it with a different email address.

I should add that I cannot use CLR for this. That would be too easy.

I'll take other suggestions, but the best I've come up with is to replace everything between the "s before and after the @.

DECLARE @testEmailAddress varchar(255);
SET @testEmailAddress = 'MyReplacementEmail@gmail.com'; 
UPDATE #TestEmails
SET 
    COMM_DATA = 
        STUFF (
            COMM_DATA,      
            PATINDEX(
                '%@%', 
                COMM_DATA
            ) 
            -
            PATINDEX ('%"%',
                REVERSE(
                    SUBSTRING(
                        COMM_DATA, 
                        0, 
                        PATINDEX(
                            '%@%', 
                            COMM_DATA
                        )
                    )
                )
            ) + 1,
            PATINDEX(
                '%@%', 
                COMM_DATA
            ) 
            +
            PATINDEX ('%"%',
                SUBSTRING(
                    COMM_DATA, 
                    PATINDEX(
                        '%@%', 
                        COMM_DATA
                    ),
                    LEN(COMM_DATA)
                )
            ) - 1,
            @testEmailAddress
        )
;

Which almost works, except that the replacement email address seems to be overrunning the replacement string by several characters.

For example

{"CustomerEmail":"original@test.com","property2":"value2","property3":"value3","property4":"value4","property5":"value5","property6":"value6","property7":"value7","property8":"value8"}

becomes

{"CutomerEmail":"MyReplacementEmail@gmail.comproperty8":"value8"}

but I expect it to be

{"CustomerEmail":"MyReplacementEmail@gmail.com","property2":"value2","property3":"value3","property4":"value4","property5":"value5","property6":"value6","property7":"value7","property8":"value8"}

Answer 1

I would do something more like this: Although I bet in SQL 2016 there is a better way to query and replace data than plain string parsing.

DECLARE @replace varchar(255) = '"MyReplacementEmail@gmail.com"'; 

DECLARE @JSON VARCHAR(4000) = '{"CustomerEmail":"original@test.com","property2":"value2","property3":"value3","property4":"value4","property5":"value5","property6":"value6","property7":"value7","property8":"value8"}'

DECLARE @Search VARCHAR(128) = '"CustomerEmail"'

DECLARE
    @SearchStart  INT
,   @SearchEnd    INT
,   @ValStart     INT
,   @ValEnd       INT
;

SELECT 
    @SearchStart = CHARINDEX(@Search, @JSON, 1) 
,   @SearchEnd = CHARINDEX(@Search, @JSON, 1) + LEN(@Search) 
,   @ValStart = CHARINDEX(':', @JSON, CHARINDEX(@Search, @JSON, 1) + LEN(@Search)) + 1 
,   @ValEnd = CHARINDEX('"', @JSON, CHARINDEX(@Search, @JSON, 1) + LEN(@Search) + 2) + 1 

SELECT STUFF(@Json, @ValStart, @ValEnd - @ValStart, @replace)

I could essentially make this a scalar function that takes a string(varchar JSON) and returns an altered JSON. You essentially are just key pair hunting in a string. That way I could run it against a result set and do it N number of times and just require a parameter of what field I was searching(@JSON), what I was 'Key' I was searching to replace(@CustomerEmail), and what I was replacing it with(@replace).

Answer 2

CROSS APLLY is handy for calculations like this one

declare @t table (comm_data varchar(max))
insert @t values
('{"CustomerEmail":"original@test.com","property2":"value2","property3":"value3","property4":"value4","property5":"value5","property6":"value6","property7":"value7","property8":"value8"}');

DECLARE @testEmailAddress varchar(255);
SET @testEmailAddress = 'MyReplacementEmail@gmail.com'; 
select t1.*, t2.*, stuff(comm_data,p1-p2+2,p2+p3-3,@testEmailAddress)
from @t 
cross apply (
     select p1=PATINDEX('%@%',COMM_DATA)
    ) t1
cross apply (
     select p2=PATINDEX('%":"%', reverse(left(COMM_DATA,p1))),
       p3 = PATINDEX('%","%', substring(COMM_DATA, p1, len(COMM_DATA)))
    ) t2

You can easily debug proper p1-p2+2,p2+p3-3 stuff bounds and convert the code to a long form if needed.

Answer 3

I was miscalculating the end point. Instead of giving the distance between the start and end points for the STUFF, I was giving it the starting and ending positions from the start of the string. Final code looks like:

    COMM_DATA = 
        STUFF (
            COMM_DATA,      
            PATINDEX(
                '%@%', 
                COMM_DATA
            ) 
            -
            PATINDEX ('%"%',
                REVERSE(
                    SUBSTRING(
                        COMM_DATA, 
                        0, 
                        PATINDEX(
                            '%@%', 
                            COMM_DATA
                        )
                    )
                )
            )+ 1,
            PATINDEX(
                '%@%', 
                COMM_DATA
            ) 
            +
            PATINDEX ('%"%',
                SUBSTRING(
                    COMM_DATA, 
                    PATINDEX(
                        '%@%', 
                        COMM_DATA
                    ),
                    LEN(COMM_DATA)
                )
            )
            -
            (
                PATINDEX(
                    '%@%', 
                    COMM_DATA
                ) 
                -
                PATINDEX ('%"%',
                    REVERSE(
                        SUBSTRING(
                            COMM_DATA, 
                            0, 
                            PATINDEX(
                                '%@%', 
                                COMM_DATA
                            )
                        )
                    )
                )
            ) - 2,
            @testEmailAddress
        )