Kbase P121793: SQL-92 SUBSTRING function behavior change in OpenEdge
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
25/01/2007 |
|
Status: Unverified
GOAL:
Did the SQL-92 SUBSTRING function behavior change in OpenEdge?
GOAL:
When did the behavior of the SQL-92 SUBSTRING function change in OpenEdge?
GOAL:
How did the behavior of the SQL-92 SUBSTRING function change in OpenEdge?
GOAL:
Why did the behavior of the SQL-92 SUBSTRING function change in OpenEdge?
FACT(s) (Environment):
OpenEdge 10.1A
CAUSE:
Yes, the behavior of the SQL-92 SUBSTRING function has changed starting with OpenEdge 10.x The change was because Version 9.x was incorrectly handling a SUBSTRING function applied to a 0-length character value by returning the Null value for a character value whose length is 0 which is incorrect, and not in compliance with the SQL-1999 or with SQL-92 standard. The correct result is to return a character value whose length is 0. That is, SUBSTRING on a 0-length string should give a 0-length string.
FIX:
OpenEdge 10.x SQL-92 function returns the correct standards compliant result. Legacy queries using Version 9.x SQL-92 SUBSTRING function may need to be modified to accommodate the new correct behavior. The NULLIF function may also be used to obtain the old Version 9.x SQL-92 SUBSTRING function behavior. For example:
SUBSTRING( NULLIF (Column_Name,''), 1,2)