REPLACE() Function as CASE-Sensitive
As stated earlier, REPLACE function is case insensitive. In some of the cases, we want only some particular case data to be replaced say it to be upper case or lower case. We can solve this problem very easily. We will use binary collation. Doing this, REPLACE() function will treat characters differently based on their binary values.
Query:
SELECT REPLACE('JAVA is good' COLLATE Latin1_General_BIN, 'java', 'Python') as RESULT
Output:
Explanation: In the above image, you can see that the input string remains unchanged. We have specified ‘java‘ in lower case but in upper case, JAVA is present in the string. To replace the word, we need to pass the exact word keeping the case unchanged.
Query:
SELECT REPLACE ( 'JAVA is good' COLLATE Latin1_General_BIN, 'JAVA' , 'Python') as RESULT
Output:
Explanation: We can see that the string has been updated now. In this case, we have specified the same case as in the given string string. Therefore the substring string has been matched and get replaced by our REPLACE() function.
SQL Server REPLACE() Function
SQL Server is a strong relational database management system (RDBMS) developed to manage large data efficiently. In SQL Server, the REPLACE() function is used to modify or replace a substring within a given string. Taking about the real word uses, the REPLACE() function is vastly used in data processing tasks such as data cleaning. It is also used for data masking purposes.
In this article, we are going to deep dive into the uses and implementation of the REPLACE() function. We will explore various examples along with their respective explanations.