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
..."
midrange.com
