Use of the Replace function in SQL Server

In the use of SQL, we occasionally encounter a situation, that is, the need to change the form of data storage, such as a database of a table (Info) has a field educational (education), previously stored in the JSON array, now because of changes in requirements, I need to change the storage form of the data to JSON format, so that we need to replace the data, when the amount of data is too large, manual operation is obviously undesirable, so the author found in the SQL of the Replace function, the following share my personal experience.

REPLACE

Replaces all occurrences of the second given string expression in the first string expression with the third expression.

Grammar

REPLACE (' String_replace1 ', ' string_replace2 ', ' string_replace3 ')

Parameters

"' String_replace1 '"

The string expression to search for. String_replace1 can be either character data or binary data.

"' String_replace2 '"

The string expression to find. String_replace2 can be either character data or binary data.

"' String_replace3 '"

The string expression to replace. String_replace3 can be either character data or binary data.

return type

If String_replace (1, 2, or 3) is one of the supported character data types, the character data is returned.

Returns binary data if String_replace (1, 2, or 3) is one of the supported binary data types.

Example

The following example replaces the string CDE in Abcdefghi with XXX.

SELECT REPLACE (' abcdefghicde ', ' cde ', ' ' xxx ') go

Here is the result set:

------------abxxxfghixxx (1 row (s) affected)

So how do you make changes directly to the data?

First, we want to implement the data stored in the table to modify, then there must be Update, and secondly, we need to replace our data, then there will be replace.

The resulting SQL statement is as follows:

Update [Info] set [Educational] = (select replace ([educational], ' [', '), '] ', ')

Executing the SQL statement above will change all the data stored in the table from the JSON array to the JSON string.