RE: SQL Reverse function

Gad,
I find that I enjoy solving unique problems, so here is a pure SQL function which should take care of your formatting.
CREATE FUNCTION YOURLIB.REVERSEHEB ( INSTR VARCHAR(4000)) RETURNS VARCHAR(4000) LANGUAGE SQL DETERMINISTIC CONTAINS SQL RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION SET OPTION DBGVIEW = *SOURCE BEGIN ATOMIC DECLARE CHARSTR, NBRSTR, HLDSTR, REVSTR VARCHAR(4000) DEFAULT ''; DECLARE LEN INT DEFAULT 0; SET(CHARSTR, NBRSTR, LEN) = (TRIM(INSTR), TRANSLATE(TRIM(INSTR), X'41', X'404A4B4C4D4E4F505A5B5C5D5E5F60616A6B6C6D6E6F797A7B7C7D7E7F818283848586878889919293949596979899A1A2A3A4A5A6A7A8A9B0BBBAC0C1C2C3C4C5C6C7C8C9D0D1D2D3D4D5D6D7D8D9E0E2E3E4E5E6E7E8E9', X'41'), LENGTH(TRIM(INSTR))); WHILE LEN > 0 DO IF SUBSTR(NBRSTR,1,1) <> X'41' THEN SET(REVSTR, CHARSTR, HLDSTR, NBRSTR, LEN) = (REVSTR, SUBSTR(CHARSTR, 2, LEN-1), HLDSTR CONCAT SUBSTR(NBRSTR,1,1), SUBSTR(NBRSTR, 2, LEN-1), LEN-1); ELSE SET(REVSTR, CHARSTR, HLDSTR, NBRSTR, LEN) = (SUBSTR(CHARSTR, 1, 1) CONCAT TRIM(HLDSTR) CONCAT REVSTR, SUBSTR(CHARSTR, 2, LEN-1), ' ', SUBSTR(NBRSTR, 2, LEN-1), LEN-1); END IF; END WHILE;RETURN TRIM(HLDSTR) CONCAT TRIM(REVSTR);END;


Usage:
SelectYOURLIB.REVERSEHEB('1MAIN ST12')From(Values 1) As A;

Returns:12TS NIAM1

Please let the list know if it works for you.
Jim

Date: Thu, 2 Jan 2014 17:55:08 +0200
Subject: Re: SQL Reverse function
From: gadmiron@xxxxxxxxx
To: midrange-l@xxxxxxxxxxxx

Hello guys,

Apologies, due to end of year rush I had to neglect this forum.

Yes it is a very peculiar local (Bidi) situation with CCSID 424 (Hebrew)

Hebrew is read Right to left so description text fields are populated
Right to left starting at the right-most pos (424 is I believe
Visual Hebrew).
Numbers however, we read left to right.

English letters Address example:

MAIN ST12 is stored as
|_|*1*|*2*|*T*|*S*|_|*N*|*I*|*A*|*M*|

SIDE ST8 is stored as
|_|*_*|*8*|*T*|*S*|_|*E*|*D*|*I*|*S*|

Now,
when displaying/printing the address we use the raw data
but in order to sort correctly we need to Reverse the data
otherwise SIDE ST8 will precede MAIN ST12 due to "SIDE ST8" 's shorter
length.

Select CustNo, CustName, CustAddress
from CustMast
order by Reverse(CustAddress)

Simply flipping the data will get you MAIN ST21 instead of MAIN ST12
So numbers (and English words as well) should remain intact.

Hope this clears the issue

Gad

1. Re: SQL Reverse Function (CRPence)


----------------------------------------------------------------------

message: 1
date: Fri, 20 Dec 2013 11:12:00 -0800
from: CRPence <CRPbottle@xxxxxxxxx>
subject: Re: SQL Reverse Function

On 20-Dec-2013 07:28 -0800, Vernon Hamberg wrote:

On 12/19/2013 11:21 PM, John Yeung wrote:

On Thu, Dec 19, 2013 at 10:38 PM, Gad Miron wrote:

I neglected to mention that there might be numbers embedded in
the string, The numbers needs to remain intact.
Given a 'ab123cd' string, a simple reverse will get me cd321ba
instead of dc123ba

That is quite an unusual requirement. If I may ask, what do you
need that for?

Good question - I can think of one possibility - if the string is
overloaded with multiple values - region, district, area (not good
examples, I know), then there may be a desire to leave certain items
in situ and swap others for some reason.

Which raises for me the thought that this is VERY specific to a local
requirement and doesn't line up well for generalization. If the
string were 'a1b2c3d', then by Gad's requirement, reversing alpha
only would be 'd1c2b3a'.

Or for an attempt to display data that is apparent BiDirectional
(BIDI) data; i.e. reformat the data currently stored as left-to-right
data to be presented\read as right-to-left data, while leaving any
embedded /numeric/ data in-tact. But the requirements would be much
more complicated than as-stated\alluded. Even if not so complex, there
is still way too little information to make a good suggestion. I have a
function that does exactly what both of those _very simple examples_
imply would be the expected result, but I would be hesitant to share
that as a possible solution without knowing what are the requirements;
i.e. overly-simplified examples are not requirements.

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.