Forum Stats

  • 3,815,823 Users
  • 2,259,094 Discussions
  • 7,893,259 Comments

Discussions

REGEXP_substr in 9i?

User_WWD0C
User_WWD0C Member Posts: 9 Green Ribbon

i'm looking for REGEXP_substr  in 9i, any alternative or custom function if available

«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,911 Red Diamond

    Hi, @User_WWD0C

    The package OWA_PATTERN has some pattern-matching functions that work in version 9. See OWA_PATTERN (oracle.com) or look it up in your version of the documentation.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown

    I remember getting the java and uploading into db for regexpsubstr, but that was way back... a 8i is OLD.

    Depending on your as yet unknown requirement you may be able to acheive what ever it is you are trying to achieve, using a combination of SUBSTR() and INSTR()

  • User_WWD0C
    User_WWD0C Member Posts: 9 Green Ribbon

    REGEXP_substr(korr_mnogochlen,'[^,]+',1,i)

    this is the expression and i dont know to use in with SUBSTR() and INSTR()

  • User_H3J7U
    User_H3J7U Member Posts: 1,060 Gold Trophy

    REGEXP_substr(korr_mnogochlen,'[^,]+',1,i)

    trunc(number)

  • User_WWD0C
    User_WWD0C Member Posts: 9 Green Ribbon

    u mean instead of REGEXP_substr(korr_mnogochlen,'[^,]+',1,i)

    use trunc(korr_mnogochlen)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,911 Red Diamond

    hi, @User_WWD0C

    Did you mean substr(korr_mnogochlen,'[^,]+',1,1), where the last argument is the number 1? If so, here's one way to do it:

    SELECT    SUBSTR ( LTRIM (korr_mnogocHlen, ',')
    	 	 , 1
    		 , INSTR ( LTRIM (korr_mnogochlen, ',') || ','
    		  	 , ','
    			 ) - 1
    		 ) AS km1
    FROM	  table_x
    ;
    

    If you know that the string never begins with ',', then you don't need LTRIM.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,911 Red Diamond

    If i is a number, you can do something like this:

    WITH    got_pos    AS
    (
    	SELECT  LTRIM (korr_mnogochlen, ',')	AS str
    	,	INSTR ( ',' || LTRIM (korr_mnogochlen, ',') || ','
    		      , ','
    		      , 1
    		      , i
    		      )				AS pos_1
    	,	INSTR ( LTRIM (korr_mnogochlen, ',') || ','
    		      , ','
    		      , 1
    		      , i
    		      )				AS pos_2
    	FROM   table_xi
    )
    SELECT    SUBSTR ( str
    	 	 , pos_1
    		 , pos_2 - pos_1
    		 )	AS kmi
    FROM	  got_pos
    ,C';
    

    assuming korr_mnogochlen doesn't contain multiple consecutive ','s, e.g. 'A,,,B,C'. If it does, you need an additional step.

    If you'd care to post some sample data (CREATE TABLE and INSERT statements) and the desired results, then I could test my ideas.

  • User_WWD0C
    User_WWD0C Member Posts: 9 Green Ribbon

    i m making a qr code,

    and yes this would be a number

         case  

          when korr_byte=7 then 

          korr_mnogochlen:='87, 229, 146, 149, 238, 102, 21';     

          when korr_byte=10 then  

          korr_mnogochlen:='251, 67, 46, 61, 118, 70, 64, 94, 32, 45';       

          when korr_byte=13 then  

          korr_mnogochlen:='74, 152, 176, 100, 86, 100, 106, 104, 130, 218, 206, 140, 78';

          when korr_byte=15 then 

          korr_mnogochlen:='8, 183, 61, 91, 202, 37, 51, 58, 58, 237, 140, 124, 5, 99, 105';

          when korr_byte=16 then  

          korr_mnogochlen:='120, 104, 107, 109, 102, 161, 76, 3, 91, 191, 147, 169, 182, 194, 225, 120';

          when korr_byte=17 then 

          korr_mnogochlen:='43, 139, 206, 78, 43, 239, 123, 206, 214, 147, 24, 99, 150, 39, 243, 163, 136';

          when korr_byte=18 then  

          korr_mnogochlen:='215, 234, 158, 94, 184, 97, 118, 170, 79, 187, 152, 148, 252, 179, 5, 98, 96, 153';

          when korr_byte=20 then 

          korr_mnogochlen:='17, 60, 79, 50, 61, 163, 26, 187, 202, 180, 221, 225, 83, 239, 156, 164, 212, 212, 188, 190';

          when korr_byte=22 then  

          korr_mnogochlen:='210, 171, 247, 242, 93, 230, 14, 109, 221, 53, 200, 74, 8, 172, 98, 80, 219, 134, 160, 105, 165, 231'; 

          when korr_byte=24 then 

          korr_mnogochlen:='229, 121, 135, 48, 211, 117, 251, 126, 159, 180, 169, 152, 192, 226, 228, 218, 111, 0, 117, 232, 87, 96, 227, 21';

          when korr_byte=26 then  

          korr_mnogochlen:='173, 125, 158, 2, 103, 182, 118, 17, 145, 201, 111, 28, 165, 53, 161, 21, 245, 142, 13, 102, 48, 227, 153, 145, 218, 70';

          when korr_byte=28 then  

          korr_mnogochlen:='168, 223, 200, 104, 224, 234, 108, 180, 110, 190, 195, 147, 205, 27, 232, 201, 21, 43, 245, 87, 42, 195, 212, 119, 242, 37, 9, 123';                       

          when korr_byte=30 then 

          korr_mnogochlen:='41, 173, 145, 152, 216, 31, 179, 182, 50, 48, 110, 86, 239, 96, 222, 125, 42, 173, 226, 193, 224, 130, 156, 37, 251, 216, 238, 40, 192, 180'; 

         end case;

    if does not contains any  'A,,,B,C'.

    if have done similar to what u suggested

          FOR i IN 1..korr_byte LOOP

          korr_mnogochlen_bin:=korr_mnogochlen_bin||lpad( xx_make_qr.hex2bin(xx_make_qr.dec2hex( SUBSTR ( LTRIM (korr_mnogocHlen, ',') , 1 , INSTR ( LTRIM (korr_mnogochlen, ',') || ',' , ',' ) - 1 )   ) ) ,8,'0');--lpad( hex2bin(dec2hex( REGEXP_substr(korr_mnogochlen,'[^,]+',1,i)) ) ) ,8,'0');

          cor10(i):= SUBSTR ( LTRIM (korr_mnogocHlen, ',') , 1 , INSTR ( LTRIM (korr_mnogochlen, ',') || ',' , ',' ) - 1 );

          END LOOP;


    but when i read the image from qr reader, it does not read anything , how i can use your above logic into this ?

    very help much appreciated

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,911 Red Diamond

    Hi, @User_WWD0C

    but when i read the image from qr reader, it does not read anything , how i can use your above logic into this ?

    Sorry, I don't know what the image is, or anything about qr reader. The query in my last message shows how to get the i-th comma-delimited substring for a given i. If you need to get all the comma-delimited substrings and convert them to numbers, I can help with that. Post CREATE TABLE and INSERT statements for a little sample data, and the exact results you want from that data.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,328 Red Diamond

    Create type & function:

    CREATE OR REPLACE
      TYPE STRING3_LIST
        IS TABLE OF VARCHAR2(3)
    /
    CREATE OR REPLACE
      FUNCTION SPLIT_AND_TRIM(
                              P_STR   VARCHAR2,
                              P_DELIM VARCHAR2 DEFAULT ',',
                              P_TRIM  VARCHAR2 DEFAULT ' '
                             )
        RETURN STRING3_LIST
        IS
            V_START_POS NUMBER := 1;
            V_END_POS   NUMBER := 0;
            V_RES       STRING3_LIST := STRING3_LIST();
            V_DELIM_LEN NUMBER := LENGTH(P_DELIM);
        BEGIN
            LOOP
               V_END_POS := INSTR(P_STR || P_DELIM,P_DELIM,V_START_POS);
               EXIT WHEN V_END_POS = 0;
               V_RES.EXTEND;
               V_RES(V_RES.COUNT) := RTRIM(LTRIM(SUBSTR(P_STR,V_START_POS,V_END_POS - V_START_POS),P_TRIM),P_TRIM);
               V_START_POS := V_END_POS + V_DELIM_LEN;
            END LOOP;
            RETURN V_RES;
    END;
    /
    

    Now you can easily split and trim (I used brackets just to show values are trimmed):

    set serveroutput on
    declare
        v_korr_mnogochlen varchar2(1000);
        v_list            string3_list;
    begin
        v_korr_mnogochlen :='168, 223, 200, 104, 224, 234, 108, 180, 110, 190, 195, 147, 205, 27, 232, 201, 21, 43, 245, 87, 42, 195, 212, 119, 242, 37, 9, 123';
        v_list := split_and_trim(v_korr_mnogochlen);
        for v_i in 1..v_list.count loop
          dbms_output.put_line('[' || v_list(v_i) || ']');
        end loop;
    end;    
    /
    [168]
    [223]
    [200]
    [104]
    [224]
    [234]
    [108]
    [180]
    [110]
    [190]
    [195]
    [147]
    [205]
    [27]
    [232]
    [201]
    [21]
    [43]
    [245]
    [87]
    [42]
    [195]
    [212]
    [119]
    [242]
    [37]
    [9]
    [123]
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL>
    

    SY.