Re: SQL Reverse Function

On 19-Dec-2013 09:58 -0800, Tom Stieger wrote:

On Thu, Dec 19, 2013 at 7:42 AM, Gad Miron wrote:

Is there a SQL (scalar) function to reverse a string like in:
Select name, reverse(name) from myFile
result:
abc cba

The following seemed to work for my on v6.1:

CREATE FUNCTION REVERSE
(INFIELD VARCHAR(128) )
RETURNS VARCHAR(128)
LANGUAGE SQL
BEGIN
DECLARE outField VARCHAR(128) DEFAULT '';
DECLARE i INTEGER DEFAULT 0;
DECLARE fieldLength INTEGER;
SET fieldLength = LENGTH(INFIELD);
WHILE i<= fieldLength DO
SET outField = outField || SUBSTR(INFIELD, fieldLength-i, 1);
SET i = i + 1;
END WHILE;
RETURN outField;
END;

Testing it:
VALUES(REVERSE('THOMAS'));

Results:
SAMOHT

While not helpful for the updated requirements made to the OP [in a followup reply], I offer...

FWiW: The last concatenation in the above code is always adding a blank; i.e. the expression "fieldLength-i" on the last iteration always evaluates to zero, so the substring scalar becomes effectively the request to "SUBSTR(INFIELD, 0, 1)" which evaluates to an empty string, padded with one blank to produce the CHAR(1) result. Thus the WHILE condition probably should use a less-than rather than less-than-or-equal for comparison.

FWiW: Using the COBOL example that I referenced in an earlier reply [link follows], as an external UDF, performed significantly faster [clock time] in effecting the same pure reversal of bytes [see Note: below], per results of some simple tests on v5r3:
http://archive.midrange.com/midrange-l/201312/msg00654.html
"... an example of such a procedure here:
http://www.code400.com/forum/archive/index.php?t-9943.html
Author: Mercury December 17th, 2010, 11:03 AM
'I wrote a Cobol function using the Cobol Built-in FUNCTION REVERSE() many moons ago...'
"

I literally copied\pasted the ILE COBOL source [provided by Mercury in the above referenced discussion], but rather than defining an interface to the RPG as shown in that discussion, I defined an interface to the SQL using effectively the following [of course I would not actually use that name, with the presumption that the word REVERSE might eventually be claimed by the SQL as a Reserved Word; probably for a builtin scalar]:

create function Reverse
( inpString varchar( 1024) )
returns varchar( 1024)
language cobolle no sql returns null on null input
external name 'THE_LIB/THE_SRVPGM(REVERSE)'
parameter style general
;

Note: I am under the impression that the use of the intrinsic function REVERSE, as coded in that discussion, may not support DBCS. That is, I expect the effect is not actual /character-reversal/, but is instead /byte-reversal/, even though DBCS or National [Language] _character_ support is available for that intrinsic function REVERSE according to the docs:
<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzasb/sc092539948.htm>
"...
IBM® Extension
Argument-1 can be DBCS or national.
End of IBM® Extension
..."