SQL replace and calculate character length and replace again

I want to do an update on a Firebird table. So far not a problem:

update MYTABLE
set params = replace(params, 'SOMEHOST', 'SOMENEWHOST')

Before the update statement my params look like this:

s:91:"{"server":"SOMEHOST","port":"21","ssl":false,"user":"DUMMY","pwd":"SECRET","path":"FOLDER"}";

After my update the params look like this:

s:91:"{"server":"SOMENEWHOST","port":"21","ssl":false,"user":"DUMMY","pwd":"SECRET","path":"FOLDER"}";

So far it’s ok.

But the frontend program can not handle this because there is a checksum in the params.

The value s:91 is the character length from the opening brace to the closing one.

From row to row the character length can differ because there is another path, user or pwd.

Is it possible to calculate the character length from the opening brace to the closing and extend the update statement with Firebird?

Answer

You first replace the old host by the new host, and then do a second replace to set the new size prefix.

Use the position function to locate the “{” and “}” positions and build the “s:91:” and “s:94:” strings, to replace them.

update MYTABLE set 
       PARAMS = replace(replace(PARAMS, 'SOMEHOST', 'SOMENEWHOST'), 
                       's:' || cast(position('}' in PARAMS) - position('{' in PARAMS) + 1 as varchar(16)) || ':',
                       's:' || cast(position('}' in replace(PARAMS, 'SOMEHOST', 'SOMENEWHOST')) - position('{' in PARAMS) + 1 as varchar(16)) || ':')

PS: It would be much safer if when replacing the Host you also include the identifier. So if by any chance that Host name is casually a subpart of the password or folder they won’t be changed, corrupting those values.

replace(PARAMS, ‘”server”:”SOMEHOST”‘, ‘”server”:”SOMENEWHOST”‘)

instead of

replace(PARAMS, ‘SOMEHOST’, ‘SOMENEWHOST’)

That makes this result query :

update MYTABLE set 
       PARAMS = replace(replace(PARAMS, '"server":"SOMEHOST"', '"server":"SOMENEWHOST"'), 
                       's:' || cast(position('}' in PARAMS) - position('{' in PARAMS) + 1 as varchar(16)) || ':',
                       's:' || cast(position('}' in replace(PARAMS, '"server":"SOMEHOST"', '"server":"SOMENEWHOST"')) - position('{' in PARAMS) + 1 as varchar(16)) || ':')