This is strange. When I run it in Firebird Maestro 17.1 it returns empty
string. When I run it is Database Workbench 5.5 it works as expected.





From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com>
Sent: Monday, July 15, 2019 7:50 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Proper Case Function for Firebird 2.5.4 and
above





Greetings All,

We are currently on Firebird 2.5.4 and hope to move to Firebird 3.0 by the
end of this year once I determine what all needs to take place in
preparation to do so.

One of the things we did when moving from Firebird 1.5 to 2.5 was to remove
the dependencies on external UDF's from FreeAdhocUDF since Firebird at that
point contained an internal replacement for most of those that we used.

I have one UDF left to replace F_PROPERCASE(). It appears Firebird 2.5 nor
Firebird 3.0 have a replacement internal UDF for this one unless I missed
it.

So we tried to come up with a way of doing this with a few stored procedures
that my associate wrote. They are included below.

They compiled and worked great. The next day when I tried to extract the
metadata on my development database and the database on the office server to
compare them to do an update script I received an error that I need guidance
with.

First off here is how to use the stored procedures:

PROPER_CASE('firebird support group') which would return 1 value containing:
Firebird Support Group

PROPER_CASE calls SPLITTER:
SPLITTER('firebird support group', ' ') which would return 3 values in the
cursor containing:
firebird
support
group

Here are the two stored procedures:

set term ^ ;
create or alter procedure SPLITTER (
in_str varchar(8190),
in_splitter char(1))
returns (
out_str varchar(8190) )
as
declare variable iLast integer;
declare variable iNext integer;
declare variable iLen integer;
begin
iLast = 1;
iNext = position(:in_splitter, in_str, iLast);
iLen = char_length(in_str) + 1;
if (:iNext = 0) then
begin
out_str = in_str;
suspend;
end
else
begin
while (:iNext > 1) do
begin
out_str = substring(:in_str from :iLast for :iNext - :iLast);
iLast = iNext + 1;
iNext = position(:in_splitter, in_str, iLast);
suspend;
end
if (iNext = 0 and iLast > 1) then
begin
out_str = substring(:in_str from :iLast for :iLen - :iLast);
suspend;
end
end
end^
set term ; ^

GRANT EXECUTE
ON PROCEDURE SPLITTER TO SYSDBA;

set term ^ ;
create or alter procedure PROPER_CASE (
in_str varchar(8190) )
returns (
out_str varchar(8190) )
as
declare variable tmp varchar(8190);
declare variable tcase varchar(8190);
begin
out_str = '';
for select out_str from SPLITTER(:in_str, ' ') into :tmp
do begin
if (char_length(tmp) > 0) then
tcase = upper(left(tmp, 1)) || lower(right(tmp, char_length(tmp)
-1));
if (char_length(tcase) > 0 and char_length(out_str) > 0) then
out_str = out_str || ' ' || tcase;
else
out_str = tcase;
end
suspend;
end^
set term ; ^

GRANT EXECUTE
ON PROCEDURE PROPER_CASE TO SYSDBA;

****************************************************************

So what was the error?

When extracting the metadata on the production database:
Procedure SPLITTER: Invalid factor in expression (POSITION)
Script: Line:11 Pos:20

Anyone have any idea what needs to be done to correct this problem? Or does
anyone have another way to proper case a string without using F_PROPERCASE
external function?

Thanks for all who reply with advice on how to proceed.

Mike





[Non-text portions of this message have been removed]