Result data type of a REPLACE function [CR 417507]

In Chapter 5, “SQL Functions,” the REPLACE function included a new Usage section as follows:

The result data type of a REPLACE function is a LONG VARCHAR. If you use REPLACE in a SELECT INTO statement, you must have a Large Objects Management option license or use CAST and set REPLACE to the correct data type and size.

There are two ways to work around this issue:

• Declare a local temporary table and then do an INSERT:

  DECLARE local temporary table #mytable 
    (name_column char(10)) on commit preserve rows;
  INSERT INTO #mytable SELECT REPLACE(name,'0','1')   FROM dummy_table01;
  

• Use CAST:

  SELECT CAST(replace(name, '0', '1') AS Char(10)) into #mytable from dummy_table01;
  

In ESD #1, this usage also applies to the CONVERT function and string concatenation operators.

The result data type of an AES_ENCRYPT function may be a LONG VARBINARY. If you use AES_ENCRYPT in a SELECT INTO statement, you must have a Large Objects Management option license or use CAST and set AES_ENCRYPT to the correct data type and size.