REPLACE() function accepts and returns non-character data types

All we need is an easy explanation of the problem, so here it is.

The Microsoft documentation indicates that the REPLACE() function accepts and returns only character data types. However, these work without error:

SELECT   REPLACE(123456, 'X', 'Y')
;

SELECT   REPLACE(CAST(123456 AS INT), 'X', 'Y')
;

Furthermore, you can treat the result as an INT, suggesting that the function retained the argument’s data type without attempting to convert it:

SELECT   REPLACE(123456, 'X', 'Y') / 3
;

What is not being stated in the Microsoft documentation?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

To add onto what Erik stated in his comment, it doesn’t matter what data type you want to explicitly define your parameter as, the definition of the argument inside of the REPLACE() function is defined to take in a string_expression.

That means, it’ll implicitly cast the parameter to a string-based data type, regardless. That data type depends on what was actually passed in – the SQL Engine has some logic and a hierarchy it follows on what can be implicitly castable to another type, which likely follows this data type conversion chart:

REPLACE() function accepts and returns non-character data types

If you tried an incompatible type that couldn’t be implicitly converted, such as an XML object, you would then get an error with the REPLACE() function.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply