0

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?

1
  • 2
    Nothing, it's just standard implicit conversion. Jun 17 at 18:26

1 Answer 1

3

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:

enter image description here

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.

1
  • 1
    Ah, yes. This makes sense. Implicit casting. It implicitly casted the argument (from INT to VARCHAR) prior to entering the function, and then implicitly casted the result (from VARCHAR to INT) when the division operator was applied. I guess you also have to be careful because implicit casting doesn't always go as expected. For example, this SELECT REPLACE(CAST(0.0000000000123456 AS FLOAT), 'E', 'HO, HO, HO!') results in this '1.23456HO, HO, HO!-011' Jun 17 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.