In this example, the collation we specify includes _CS in its name, which means “Case Sensitive”. Case-Sensitive SELECT REPLACE('Cats, cats, and more cats!' COLLATE SQL_Latin1_General_CP1_CS_AS, 'cat', 'Dog')
You’ll notice that this method doesn’t affect the case of the replaced string. This causes all occurrences to be replaced, despite the first occurrence having uppercase characters. In this example, the collation we specify includes _CI in its name, which means “Case Insensitive”. Case-Insensitive SELECT REPLACE('Cats, cats, and more cats!' COLLATE SQL_Latin1_General_CP1_CI_AS, 'cat', 'Dog') Here’s an example that compares two collations. This can be handy for performing case-sensitive find/replace operations and the like. You can use the optional COLLATE clause to apply an explicit collation to the input. You’ll need to gauge each situation as it arises. This obviously assumes that you’re replacing the whole word. Result: My apartment has some pictures hanging on the walls and some pot plants hanging from the ceiling. You can usually safeguard against this by adding spaces around the search word, as well as the replacement word: SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', ' art ', ' pictures ') This is because the word apartment contains the substring art. However, in this case the word apartment was also affected – it has turned into apicturesment, which was not intended. Result: My appicturesment has some pictures hanging on the walls and some pot plants hanging from the ceiling.Īs you can see, we replaced the word art with pictures. It’s very easy to make mistakes when using the REPLACE() function (or any find and replace functionality for that matter).įor example, this mistake: SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'art', 'pictures') Result: My apartment has art hanging on the walls and pot plants hanging from the ceiling. We can fix this by including one of the spaces in the word to be removed: SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'some ', '') We removed the word but we didn’t remove any spaces, therefore, two spaces remain. This is because the word that we removed had spaces to its left and right. However, if you look closely, you’ll see that the new string contains double spaces where we removed the word. To do this, simply replace it with the empty string: SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'some', '') You can also remove a word (or substring) from the string.
So here we replace the word some with the words lots of. Result: My apartment has lots of art hanging on the walls and lots of pot plants hanging from the ceiling. So we could easily have replaced that one word with two or more words: SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'some', 'lots of') After all, we’re simply replacing a string with another string, regardless of whether that string contains words, letters, numbers, spaces, etc. Of course, there’s no rule that says you can only replace a word with one word (and vice-versa).
So in this example we simply replace the word some with the word no. Result: My apartment has no art hanging on the walls and no pot plants hanging from the ceiling. Here’s an example to demonstrate: SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'some', 'no') Where string_expression is the string that contains one or more instances of the string (or substring) to replace, string_pattern is the string to replace, and string_replacement is the string to replace it. Here’s the official syntax: REPLACE ( string_expression, string_pattern, string_replacement ) For example, you can replace all occurrences of a certain word with another word. In SQL Server, you can use the T-SQL REPLACE() function to replace all instances of a given string with another string.
0 kommentar(er)
