Hi, Try putting parenthesis
substring( input_string from (idx-1) for 1 ) Regards, KarolBieniaszewski ----- Reply message ----- Od: "Raith,Daniel" <[email protected]> Do: "[email protected]" <[email protected]> Temat: [firebird-support] for select stored procedure question Data: czw., paź 10, 2013 22:57 I guess I was actually on FB2.5. After switching back to FB1.5, I think I'm out of luck because the SUBSTRING internal function requires integer literals. So this statement isn't possible: substring( input_string from idx-1 for 1 ) Since most of our servers are running 1.5 and I can't control updates, and I can't use UDF's for distribution reasons, I think I'll have to do this outside of Firebird. Unless I write a substring_proc, hmmm. From: [email protected] [mailto:[email protected]] On Behalf Of Raith,Daniel Sent: Thursday, October 10, 2013 2:38 PM To: [email protected] Subject: RE: [firebird-support] for select stored procedure question That works, but only if my stored proc contains the suspend. SET TERM ^ ; CREATE OR ALTER PROCEDURE INITIAL_CAPS_PROC ( input_string varchar(100)) returns ( output_string varchar(100)) as declare variable input_length integer; declare variable idx integer; begin output_string = ''; input_length = char_length(input_string); if (input_length > 0) then begin idx = 1; while (idx <= input_length) do begin if (idx = 1) then output_string = upper( substring( input_string from 1 for 1 ) ); else begin if ( substring( input_string from idx-1 for 1 ) = ' ' ) then output_string = output_string || upper( substring( input_string from idx for 1 ) ); else output_string = output_string || lower( substring( input_string from idx for 1 ) ); end idx = idx + 1; end end suspend; end^ SET TERM ; ^ From: [email protected] [mailto:[email protected]] On Behalf Of Woody Sent: Thursday, October 10, 2013 1:27 PM To: [email protected] Subject: Re: [firebird-support] for select stored procedure question As long as the initial_caps_proc doesn't have a suspend statement, it will basically be seen as a function call. To use it to update records one time: Update MyTable set Column_A = (select OutVar from initial_caps_proc(Column_A)), Column_B = (select OutVar from initial_caps_proc(Column_B)); I haven't tested this but it should work if memory serves me correctly. :) Woody (TMW) [Non-text portions of this message have been removed]
