V5R2 SQL gotcha with SUBSTR

[David Abramowitz]

I'm curious: What is the ANSI standard for SUBSTR? IBM has gone to great lengths to ensure that DB2/400 has a greater degree of ANSI compliance than any other RDBMS. Dave

[Guest.Visitor]

Ken Rokos wrote: > In V5R2, the SUBSTR function no longer returns an error if the offset > and/or length are out of range. For example, let's say file X has > field Y which is 5 characters long. This will not fail in V5R2: > > select substr(y,20,30) from x > > It'll successfully return a 30-character blank value instead. I don't know if I'd call this a Gotcha. I'd put this in the same category as returning a zero result when a calculation divides by zero. bill

[Guest.Visitor]

It's different than it has always been before. It is something that can catch the unwary. It's as if dividing by zero used to always cause an MCH error that you could monitor, and now it succeeds and returns zero. It's not what you'd expect or be used to.

[Guest.Visitor]

In V5R2, the SUBSTR function no longer returns an error if the offset and/or length are out of range. For example, let's say file X has field Y which is 5 characters long. This will not fail in V5R2: select substr(y,20,30) from x It'll successfully return a 30-character blank value instead. This also affects 3rd-party products like SEQUEL from ASC. The SUBSTR function in QUERY/400 still gives errors if you try out-of-range values.

[Guest.Visitor]

By the way, SUBSTR and SUBSTRING are actually different functions and can return different results, especially with DBCS and with this out-of-bounds behavior.