Re: PL/SQL Split function to recognize UTF8 multi-octets

From: Ed Kulis <ekulis_at_apple.com>
Date: Wed, 18 Dec 2002 11:36:02 -0800
Message-ID: <BA260E22.53C8%ekulis@apple.com>

Hi,

Acting on David's suggestion, a Google search found me an example which will let me parse

 10, 13, "238,161,191", "194,191" ,112, 101, 114, 108

which is great.

Here are the details of the example that I found.

--# $Id: example.comma_to_table.sql,v 1.1.1.1 2002/12/18 19:26:20 ekulis Exp
ekulis $
--# $Source: /home/ekulis/Work/wrkbin/RCS/example.comma_to_table.sql,v $

--#maintext

set serveroutput on size 1000000

--http://www.muniqsoft.de/support/plsql/plsql.htm

DECLARE

   v_tab        DBMS_UTILITY.UNCL_ARRAY;
   v_list       VARCHAR2(32000);
   v_list_new   VARCHAR2(32000);
   v_count      BINARY_INTEGER;
   v_count_new  BINARY_INTEGER;

BEGIN

• ekulis added the 3emp,4emp v_list := 'asd2,xyz,"2emp,3emp,4emp",dept,'|| 'ein_maximal_30_Zeichen_name.und_nochmal_maximal_30_zeichen'; DBMS_UTILITY.COMMA_TO_TABLE(v_list, v_count, v_tab); DBMS_OUTPUT.PUT_LINE('Ursprüngliche Anzahl der Einträge: ' || v_count); FOR i IN v_tab.FIRST.. v_tab.LAST -1 LOOP --letzter Wert: NULL DBMS_OUTPUT.PUT_LINE(v_tab(i)); END LOOP;

   v_tab(v_tab.LAST ) := 'neuer_eintrag';    v_tab(v_tab.LAST+1 ) := NULL;
   DBMS_UTILITY.TABLE_TO_COMMA ( v_tab, v_count_new, v_list_new );    DBMS_OUTPUT.PUT_LINE('Spätere Anzahl der Einträge: '

                                      || v_count_new);

   DBMS_OUTPUT.PUT_LINE('Liste: ' || v_list_new); END;
/
show errors;

Which provides the following output:

sqlplus -s /nolog <<EOS
connect $(default.acn)

set serveroutput on size 1000000

@example.comma_to_table.sql

EOS
Connected.
Ursprüngliche Anzahl der Einträge: 5
asd2
xyz
"2emp,3emp,4emp"
dept
ein_maximal_30_Zeichen_name.und_nochmal_maximal_30_zeichen Spätere Anzahl der Einträge: 6
Liste:
asd2,xyz,"2emp,3emp,4emp",dept,ein_maximal_30_Zeichen_name.und_nochmal_maxim al_3
0_zeichen,neuer_eintrag

PL/SQL procedure successfully completed.

No errors.

-ed

On 12/18/02 11:01 AM, in article BA26060C.53A9%ekulis_at_apple.com, "Ed Kulis" <ekulis_at_apple.com> wrote:

> That helps a great deal.
> 
> Thanks,
> -ed
> 
> On 12/18/02 10:20 AM, in article 3E00BC88.8B6218F6_at_exesolutions.com, "DA
> Morgan" <damorgan_at_exesolutions.com> wrote:
> 
>> 
>> If working with comma delimited data elements under 30 characters ... I don't
>> think you can do better than DBMS_UTILITY.COMMA_TO_TABLE.
>> 
>> Daniel Morgan
>> 
> 

On 12/18/02 9:31 AM, in article BA25F10D.525F%ekulis_at_apple.com, "Ed Kulis" <ekulis_at_apple.com> wrote:

> Hi,
> 
> I'm writing some functions that will illustrate the coding and characters of
> UTF8 dump strings, and ISO8859-1 codesets.
> 
> 
> I'd like to be able to parse a structure like this.
> 
> 10, 13, 238.161.191, 194.191,112, 101, 114, 108
> 
> where the comma separated elements represent the UTF8 codes which contain
> periods if the code is multi-octet. This structure will allow me to specify
> character transformations on the codes from the dump function.
> 
> I'll also need to recognize the non-delimited multi-octets from the dump
> function
> 
> 10, 13, 238, 161, 191, 194, 191, 112, 101, 114, 108
>       -------------  --------
> 
> I can hack this up with INSTR and other character functions.
> 
> But is there a clever way or function some where that will do this for me?
> 
> This is easy in perl with a split function but this task is exclusively in
> the realm of PL/SQL packages.
> 
> Here's a dream, is there a perl2plsql converter?
> 
> -ed
> 

Received on Wed Dec 18 2002 - 13:36:02 CST