Hi,

At September 8, 2004, 10:19, Bjoern Reimer wrote:

> Hi,

> Helen's book doesn't answer all questions :-)

> What does substring (Internal function) return?

> e.g.

> SELECT '#'||substring('ABC' FROM 1 FOR 10)||'#' FROM rdb$database;

> returns #ABC#

> but in a stored procedure where ABC is a varchar variable it returns
> #ABC #
> (There is no char field in the sp. Of course ABC is a value from a
> variable which is a param of the proc...)


> Why?

Because in your above SELECT statement 'ABC' would probably
interpreted as a CHAR(3) or VARCHAR(3) (I don't exactly know which one
is used), and in this case SUBSTRING has no choice than to return a
CHAR(3) or VARCHAR(3) datatype, so there wouldn't be more than 3
characters. That is the SQL standard.

And, if in your SP, the varchar parameter is of size 10 characters or
more, then that would explain the result that you are seeing in the
SP. So, essentially, if you don't want trailing spaces in this case,
then you'll need to use the UDF RTRIM or TRIM.

I hope this clears things up a bit.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)