• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 673
  • Last Modified:

Detecting all distinct characters used in all records of a column present in an Oracle table

Please, could you help me to write a stored function that, acting on a COLUMN, will give in output the different characters contained in every record with reference to that column.

In example:

If you have the table PIPPO containing the column ALPHA with these values:

ABCDE
FGHIJ
KLMNOABCDE
FGHIJ44433
1234567

... launching the SELECT:

SELECT MAGIC_FUNCTION (ALPHA) from PIPPO;

... will produce:

1234567ABCDEFGHIJKLMNO
0
hc2342uhxx3vw36x96hq
Asked:
hc2342uhxx3vw36x96hq
1 Solution
 
ajexpertCommented:
Hi,
This is not the best possible solution, but the simplest one
You can create a table in memory or a tempoary table with one column storing character

Now you can store the distinct values in the temporary table created.  Once the temporay table is populated return the rows of temporary table using ref cursor.

Please view the sample code.
--CREATE TABLE IN MEMORY OR TEMPORARY TABLE...(I have created physical ---table for simplicity)
CREATE TABLE TEMPS
( COL1 VARCHAR2(1) );
 
-- CODE AS PROCEDURE
 create or replace procedure distchar
   as
  CURSOR C1 IS
  SELECT dname from dept;
  vname VARCHAR2(2000);
  vlen NUMBER;
  vchar VARCHAR2(1);
  vcount NUMBER(1);
  begin
  FOR c1_rec in C1
  LOOP
  vname := C1_REC.DNAME;
  vlen := LENGTH(vname);
  for i in 1..vlen
  LOOP
  vchar  := (SUBSTR(VNAME,I,1));
  vcount:=0;
  SELECT count(1) into vcount from temps WHERE charcol = vchar; 
  IF vcount = 0 THEN
    INSERT INTO temps VALUES (vchar);
    COMMIT;
  END IF;
  END LOOP;
  END LOOP;
  END;
/

Open in new window

0
 
hc2342uhxx3vw36x96hqAuthor Commented:
OK ;-)
0
 
sdstuberCommented:
I know this question is long over, but it looked like an interesting question, so I had to take a crack at it.  :)

Here's my approach.  I created an aggregate function called "distinctagg"  it works just like other aggregates and is pretty quick.

This took about 2 seconds on my laptop with 74302 objects in dba_objects

select distinctagg(object_name),count(*) from dba_objects

The sorting and distinct search I used is basically just a simple insertion sort with duplicates removed.


CREATE OR REPLACE TYPE distinct_chars_type
AS
    OBJECT(
        v_result VARCHAR2(4000),
        MEMBER FUNCTION distinct_chars(p_unique IN VARCHAR2, p_new IN VARCHAR2)
            RETURN VARCHAR2
            DETERMINISTIC,
        STATIC FUNCTION odciaggregateinitialize(p_sctx IN OUT distinct_chars_type)
            RETURN NUMBER,
        MEMBER FUNCTION odciaggregateiterate(self IN OUT distinct_chars_type, p_value IN VARCHAR2)
            RETURN NUMBER,
        MEMBER FUNCTION odciaggregateterminate(
            self            IN     distinct_chars_type,
            p_returnvalue      OUT VARCHAR2,
            p_flags         IN     NUMBER
        )
            RETURN NUMBER,
        MEMBER FUNCTION odciaggregatemerge(
            self     IN OUT distinct_chars_type,
            p_ctx2   IN     distinct_chars_type
        )
            RETURN NUMBER
    );
/
 
CREATE OR REPLACE TYPE BODY distinct_chars_type
IS
    MEMBER FUNCTION distinct_chars(p_unique IN VARCHAR2, p_new IN VARCHAR2)
        RETURN VARCHAR2
        DETERMINISTIC
    IS
        v_temp    VARCHAR2(32767) := NULL;
        v_index   INTEGER;
        v_char    CHAR(1);
    BEGIN
        v_temp   := p_unique;
 
        FOR i IN 1 .. NVL(LENGTH(p_new), 0)
        LOOP
            v_char   := SUBSTR(p_new, i, 1);
 
            IF NVL(INSTR(v_temp, v_char), 0) = 0
            THEN
                v_index   := 1;
 
                WHILE v_index <= NVL(LENGTH(v_temp), 0)
                LOOP
                    IF v_char < SUBSTR(v_temp, v_index, 1)
                    THEN
                        v_temp   :=
                            SUBSTR(v_temp, 1, v_index - 1) || v_char || SUBSTR(v_temp, v_index);
                        EXIT;
                    END IF;
 
                    v_index   := v_index + 1;
                END LOOP;
 
                IF v_index > NVL(LENGTH(v_temp), 0)
                THEN
                    v_temp   := v_temp || v_char;
                END IF;
            END IF;
        END LOOP;
 
        RETURN v_temp;
    END distinct_chars;
 
 
    STATIC FUNCTION odciaggregateinitialize(p_sctx IN OUT distinct_chars_type)
        RETURN NUMBER
    IS
    BEGIN
        p_sctx   := distinct_chars_type(NULL);
        RETURN odciconst.success;
    END odciaggregateinitialize;
 
    MEMBER FUNCTION odciaggregateiterate(self IN OUT distinct_chars_type, p_value IN VARCHAR2)
        RETURN NUMBER
    IS
    BEGIN
        self.v_result   := distinct_chars(self.v_result, p_value);
        RETURN odciconst.success;
    END odciaggregateiterate;
 
    MEMBER FUNCTION odciaggregateterminate(
        self          IN     distinct_chars_type,
        p_returnvalue      OUT VARCHAR2,
        p_flags         IN     NUMBER
    )
        RETURN NUMBER
    IS
    BEGIN
        p_returnvalue   := self.v_result;
        RETURN odciconst.success;
    END odciaggregateterminate;
 
    MEMBER FUNCTION odciaggregatemerge(
        self   IN OUT distinct_chars_type,
        p_ctx2   IN     distinct_chars_type
    )
        RETURN NUMBER
    IS
    BEGIN
        self.v_result   := distinct_chars(self.v_result, p_ctx2.v_result);
        RETURN odciconst.success;
    END odciaggregatemerge;
END;
/
 
 
CREATE OR REPLACE FUNCTION distinctagg(p_input VARCHAR2)
    RETURN VARCHAR2
    PARALLEL_ENABLE
    AGGREGATE USING distinct_chars_type;
/

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now