Replacing a string pattern - SQL Server

Question

I have a VARCHAR Column called CompanyInformation that contains some Json data inside of it, here is a example of what is being stored inside of this column.

{
"tradeName": "example",
"corporateName": "example",
"phone": "example",
"nationalExpansions": [
    {
        "id": 0,
        "nameFranchise": "example",
        "phoneNumber": "example",
        "mobileNumber": "example",
        "email": "example@example.com.br"
    },
    {
        "id": 0,
        "nameFranchise": "example",
        "phoneNumber": "example",
        "mobileNumber": "example",
        "email": "example"
    },

What i have to do is replace all emails inside of this file to one specific email, i was trying to use the REPLACE function to do that, but i don`t know exactly how to do pattern matching in SQL.

UPDATE MyExampleTable SET CompanyInformation = REPLACE(CONVERT(VARCHAR(MAX), CompanyInformation), '"email": "%%"', '"email": "tests@gmail.com.br"')

But it doesn't work, the UPDATE gets executed, but he never replace the email information because he doesn't find any matching pattern.

What version of SQL Server are you working with? for 2016 or higher, you might be able to use the built in Json_update function. — Zohar Peled, Mar 23 '20 at 13:39
Unfortunately SQL Server has no pattern replacement functionality. It does have Pattern Matching, however that tells you were the start of the pattern is, and not where it ends. For a pattern that uses variable length wildcards, that may mean what you want isn't (easily) achievable. You may well be better off looking for a CLR REGEX function for this. — Thom A, Mar 23 '20 at 14:15
So, which version of SQL Server are you using? Unfortunately, STRING_SPLIT is available from Version 2016 and up only (as far as I know)... anyways, XML might do the trick. See the following thread for details: https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012 — Tyron78, Mar 23 '20 at 15:03

score 0 · Accepted Answer · answered Mar 25 '20 at 17:06

After some research and thanks to the help of a teammate, we've managed to find a solution to this problem, so i will post here to help anyone with the same problem.

This is the script:

DECLARE @text VARCHAR(max)
DECLARE @start INT = 0
DECLARE @begin INT = 0
DECLARE @end INT = 0
DECLARE @Id INT

DECLARE curEmail CURSOR
FOR
SELECT Id, CompanyInformation
FROM MyExampleTable
--WHERE id = 1

OPEN curEmail

FETCH NEXT FROM curEmail INTO @Id, @text

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @start = CHARINDEX('"email":"', @text) + 9;

    SET @begin = @start

    WHILE 1 = 1
    BEGIN
        --print 'end='+cast(@end as varchar(4))
        SELECT @start = CHARINDEX('"email":"', @text, @end) + 9;

        IF @begin <= @start
            SET @begin = @start
        ELSE
            BREAK
        SELECT @end = CHARINDEX('"', @text, @start)

        SET @text = STUFF(@text, @start, @end - @start, 'tests@gmail.com.br')

    END

    print @text
    update MyExampleTable set CompanyInformation= @text where id =@id

    FETCH NEXT FROM curEmail INTO @Id, @text

END

CLOSE curEmail

DEALLOCATE curEmail

Replacing a string pattern - SQL Server

1 Answers1