Forum Stats

  • 3,815,621 Users
  • 2,259,062 Discussions
  • 7,893,186 Comments

Discussions

Categories

REGEXP_substr in 9i?

User_WWD0C
User_WWD0C Member Posts: 9 Green Ribbon
in SQL & PL/SQL

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

Tagged:
· Share on TwitterShare on Facebook
«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.

    · Share on TwitterShare on Facebook
  • 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()

    · Share on TwitterShare on Facebook
  • 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()

    · Share on TwitterShare on Facebook
  • User_H3J7U
    User_H3J7U Member Posts: 1,060 Gold Trophy

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

    trunc(number)

    · Share on TwitterShare on Facebook
  • User_WWD0C
    User_WWD0C Member Posts: 9 Green Ribbon

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

    use trunc(korr_mnogochlen)

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,911 Red Diamond

    hi, @User_WWD0C

    https://community.oracle.com/tech/developers/discussion/comment/16817935#Comment_16817935

    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.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,911 Red Diamond
    https://community.oracle.com/tech/developers/discussion/comment/16817935#Comment_16817935

    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.

    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook