Search code examples
substringinterbase

Substring error interbase

I'm having some issues with the substr function in Interbase throwing "a general SQL error arithmetic exception, numeric overflow or string truncation error".

select SUBSTR(note, 1, 150) note
from TABLE

The note field is varchar(8192), but I'd like to be able to return only the first 150 characters. I am only able to write inline SQL so using a stored procedure is not possible.

Solution

  • The most recent version of Interbase appears to be XE7 and it's documentation reads:

    substr() returns the substring of starting at position and ending at position . Note: This function can receive and return up to 80 characters, the limit on an InterBase character string.

    So the overflow error is because you're passing varchar(8192) to function which accepts varchar(80). You need to look for some other UDF lib which has a version of substr without this limitation.