Use of the Replace function in SQL Server

Source: Internet
Author: User
Tags expression first string json

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.