Use of the REPLACE function in SQL Server

In the use of SQL, we occasionally encounter such a situation that we need to change the data storage format, such as a database table (Info) there is an Educational field (education level), which previously stored a Json array. Now, due to changes in requirements, I need to change the data storage format to Json format, in this way, we need to REPLACE the data. When the data volume is too large, manual operations are obviously undesirable, so the author finds the REPLACE function in SQL, next I will share my personal experiences.

REPLACE

Replace all the second given string expressions in the first string expression with the third expression.

Syntax

REPLACE (''string _ replace1 '', ''string _ replace2'', ''string _ replace3 '')

Parameters

''String _ replace1''

String expression to be searched. String_replace1 can be character data or binary data.

''String _ replace2''

String expression to be searched. String_replace2 can be character data or binary data.

''String _ replace3''

String expression used for replacement. String_replace3 can be character data or binary data.

Return type

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

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

Example

In the following example, replace the string cde in abcdefghi with xxx.

Select replace (''abcdefghicde'', ''cde'', ''xxx') GO

The following is the result set:

------------ Abxxxfghixxx (1 row (s) affected)

So how can we directly modify the data?

On the homepage, we need to modify the data stored in the table, so there will be an Update. Secondly, we need to REPLACE our data, so there will be a REPLACE.

The SQL statement is as follows:

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

After the preceding SQL statement is executed, all the data stored in the table is changed from a Json array to a Json string.