Re: SQL Reverse function - BIDI

On 02-Jan-2014 07:55 -0800, Gad Miron wrote:

On Fri, 20 Dec 2013 11:12:00 -0800 CRPence wrote:

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.

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

I have little actual experience using BiDirectional data, though I recall testing some use of BiDi in Query/400 and some other database support. I would expect that using the existing data transformation support and\or the provided CCSID support and Sort Sequence support, would probably be more appropriate than trying to build some private transformations.?

While ORDER BY on a /visual/ BIDI CCSID might not be capable of effecting proper collation, I would expect other variants of that same data [translations, conversions, transforms; whatever term is deemed appropriate] might effect a desired sorting.?

Would the CCSID 62235 provide the proper ordering, perhaps along with SRTSEQ(*LANGIDSHR) LANGID(HEB); e.g. effectively:

Select CustNo, CustName, CustAddress
from CustMast
order by cast(CustAddress as varchar(50) ccsid 62235)

I expect that a UCS2 or UTF16 variant of that data sorts properly using an ICU sort sequence.? Would effectively the following suffice, using SRTSEQ(HE)?:

Select CustNo, CustName, CustAddress
from CustMast
order by cast(CustAddress as vargraphic(50) ccsid 13488)