I've found a couple of hits for similar concepts, but no one specifically addresses an apostrophe in the text I have a need for this --

DECODE PROVIDER ('PEOPLE'S – YORK RD' 'PCHC - BCHD ETI')

Any recommendations?

J.

This message has been edited. Last edited by: Kerry,

Double apostrophe.

 
DEFINE FILE CAR
TEST1/A20 = DECODE COUNTRY(FRANCE  'JUST A T''EST'
                          ENGLAND 'JU''ST ANOTHER TEST'
                          ELSE    'PEOPLE''S – YORK RD');
TEST2/A20 = DECODE TEST1('PEOPLE''S – YORK RD' 'PCHC - BCHD ETI'
                         ELSE                  ' ');

END
-*
TABLE FILE CAR
PRINT CAR
      COUNTRY
      TEST1
      TEST2
END

Yeah, the double apostrophe didn't work and threw some really strange errors.

String Replace is pretty doggone ugly and likely means I need to have three defined fields to make the connection (this is for a join in Data Migrator) but I may just have to go that far down the pipe to get an answer.

Thank you all!

J.

Silly question but don't you have key that you can decode on instead of the text.

Alternatively try using IF-THEN-ELSE syntax it should handle the ''.

I'm likely going to go with the if-then-else for a first start (it's only about a dozen values).

Key? I'm the guy creating the key. Once I get the data from the seven tables that were never designed to work with each other I'll have a nice set of information with a perfectly reasonable key at the front of it. That's when I hand it off to the rookies.

J.

The double apostrophe works in 5.3.6. It concerns me that it won't work in the newer releases.

I guess I should have said may not work in the newer releases.

Double apostrophe works for me in 7.6.4 Glenda, so don't worry!

I don't see why it should become unsupported as it is built in to .QUOTEDSTRING

-SET &ECHO=ALL;
-SET &VAR1 = 'PEOPLE'S - YORK RD';
DEFINE FILE CAR
TEST1/A20 = DECODE COUNTRY(FRANCE  'JUST A T''EST'
                          ENGLAND 'JU''ST ANOTHER TEST'
                          ELSE    'PEOPLE''S - YORK RD');
TEST2/A20 = DECODE TEST1(&VAR1.QUOTEDSTRING 'PCHC - BCHD ETI'
                         ELSE                  ' ');

END
-*
TABLE FILE CAR
PRINT CAR
      COUNTRY
      TEST1
      TEST2
END

Maybe John needs to retry this approach as it is the cleanest around.

All,

Maybe I'm not getting the process(my ignorance has no boundaries!),
but, when they change the data and remove the apostrophe, will the decode work???

quote:

String Replace is pretty doggone ugly and likely means I need to have three defined fields to make the connection (this is for a join in Data Migrator) but I may just have to go that far down the pipe to get an answer.

Well, you will need a minimum of 2 , with DECODE, so 1 more line of code will strain the mind????

That is why I don't hard-code WITH special characters:

Assuming the PROVIDER length is known, say, A50:

-* Remove apostrophe from all records
REM_APOS/A50 = STRREP(50, PROVIDER, 1, '''', 0, 'X', 50, REM_APOS);

-* DECODE without apostrophe
TEST1/A50 = DECODE REM_APOS ('PEOPLES - YORK RD' 'PCHC - BCHD ETI'
ELSE 'XXX');

-* Get non-DECODEd data
TEST2/A50 = IF TEST1 EQ 'XXX' THEN PROVIDER ELSE TEST1;

TEST2 is now my column for display/JOIN. If the apostrophe is removed, DECODE still works...

Tom

quote:

That is why I don't hard-code WITH special characters:

When you're faced with an application that allows free form text entry, the users will - quite rightly - take full advantage and however much you might like to avoid coding using special caharacters, sometimes the applications from which you have to report do not allow the luxury to choose!!

T

Tony,

When did DECODE from a production table turn into input from a user text box????

In answer to your "new" comment, I would use, say, for example, STRREP...

Tom,

You bit and who mentioned user text box? I mentioned "application that allows free form text entry" which could be any form of input. Even a proudction table has it's input served from some "form" of input.

As for apostrophes within user data - I normally change them using STRREP to a HEXBYT(96,'A1') or something so that it still retains " a look" of the original input.

T

Well, then, I'm happy for you. Thanks for sharing...

Sorry I didn't get back to you guys sooner - you seem to be having quite a party without me.

I have stumbled onto additional issues that seem to be muddying up the works, but it appears that the double apostrophes is not a problem in DECODE. It is indeed a problem when one of them merely looks like an apostrophe but isn't. There's also two different dash characters in unicode. This is why I get the big bucks, and the nagging headaches.

In this case the issue is not user entry fields or display, since all of this effort is for data transformation and migration. I think the heart of the matter is the tortured path of operating systems and tools used to get the data into a position where I can grab it. Somewhere along the way a poor decision is being made (likely automated) regarding which unicode values should be used to represent what. Everything looks fine, but it's not.

So once again a task booked at six hours takes five days. Software is expensive because it's technologically in the same stage as building architecture was when St. David''s Cathedral was built.

Thank you all.

J.