Skip to main content

I discovered that when using the replace transformation with the CharType that you are required to know or look up the int value of the character you want to replace and use that as the parameter.

This does not seem to be documented anywhere or be useful in any way I can think of. I assume REPLACE('abc’, char(65), 'b’) is functionally equivalent to REPLACE('abc’, 'a’, 'b’). In a Case Insensitive collation there is no difference between the two anyhow.

There is also no COLLATION or binary type support, which are things that you can do with REPLACE() that might actually be useful in certain rare cases.

 

Hi Rory 

The chartype only supports numeric values as indicated by the warning message, if you try to use non-numeric values. 

Is there a particular reason why you can’t use the stringtype replace?

 

Hi Christian,

There is no specific reason but there also doesn't seem to be a specific reason for the char type to exist (other than a very specific subset of people who prefer ASCII codes over using letters directly). As the string type also lets you use substrings where the char type is single-character only, it is just strange for most people.

@rory.smith One example of where ASCII codes are useful is CHAR(2) - a none display character (either null or empty used in AX/Oracle) which usually needs to be replaced. Since you would not be able to just type in the value, the option to input ASCII codes is necessary to replace such a character.

Hi Christian,

 

couldn't you achieve that with a unicode codepoint in a string literal? That would be SELECT N'␂' in SSMS

@rory.smith perhaps.. please feel free to submit this as an idea :)

Reply


Terms and ConditionsCookie settings