Replace %Hex with character

[Anonymous]

Hi

In my source data I have a field that contains data which has %Hex characters in it, as per this example:

     PCI20 - %C2%BFEl agente env%C3%ADa la bolsa por la cinta antes de que se sirve la pr%C3%B3xima pax%3F

What I would like to is search the string for all the % 's and for each one translate the next 2 characters to its printable character, as in replace the %3F at the end with ?

Any ideas as I am flummoxed as how to approach this ?

Best Regards,    Bill Markham

[jonathandienst]

Hi

Create a mapping table with all the substitutions and then use MapSubString().

HTH

Jonathan

[Roop]

There is no Hex conversion that I know of but it could easily be created manually (as it never changes).

I would create a Hex conversion table and use the table in an ApplyMap for the lookup of the character sets.

In order to parse the field into its constituent parts, I would use Substringcount to get the number of "%"s and perform a loop through the "%" using subfield to extract the Nth part of the array.

I hope this helps and sorry I do not have time to work it fully through

[Roop]

Jonathan's approach is much simpler I think

[jonathandienst]

Bill

I don't think you can get a space as a value in an inline load, so I would do something like this:

MapCodes:

Mapping LOAD

  Code,

  Replace(Value, '\*', ' ') As Value

Inline

[

  Code, Value

  %20%, \*

  %27%, '

     ...

];

Jonathan

[preminqlik]

how should your output will be ? ..

[Roop]

or just create it as a file so that it does not require an inline load ......

[Anonymous]

Jonathan

Your suggestion looks promising, but I do not know in advance what the hex codes will be.  The string comes from a language translation table, so the hex could any of the hex codes for characters not on a standard UK English keyboard. Initially just Spanish language, but later many more languages.

I reckon the MapSubString() you suggested is going to be useful.

But also i have found this using my friend Google:

    =chr(Num#('3F', '(HEX)'))         which from hex 3F returns it printable character ?

Now I just need to somehow mash this up into something workable.  [Maybe some cunning dollar expansion?]

Best Regards,    Bill

[jonathandienst · Accepted solution]

Bill

If your encoding is UTF-8, you should be able to automatically load all the mappings using:

MapAsciiCodes:

Mapping LOAD

     '%' & Num(RowNo() + 31, '(HEX)') As Code,

     Chr(RowNo() + 31) As Value

AutoGenerate 256 - 32;

So that will have all the printable values up to %FF (0 - 31 are not printable)

HTH

Jonathan

[Anonymous]

Jonathan

Many thanks for that cunning bit of script to autogenerate the Ascii Mapping Table.

It is all looking very promising, so thanks again.

Best Regards,     Bill