CODEWITHSUNDEEP
oracle-databaseplsql
user3706593
user3706593

Reputation: 1

in Oracle: How do I get a substring where the end position is the nth number of a given character, not the position it selves?

I have two strings: 

A_string := 'abc;def;gh;ijkl;....
B_string := '5;43;43;xyz;....

Based on the number of ";" in A_string I need to get the substring from pos. 1 to the position equivalent to the number of ";" in A_string.

eg: A_string has 2 ";" in it. Result from substring of B_string is "5;43"

I can get the number by REGEXP_COUNT, but if I use REGEXP_SUBSTR the result is "43". Is there a way to return "5;43"?

EX:

declare 
  list varchar2(2000) := '100;200;300;400;500;600;700;800';
  corr varchar2(2000) := '1;1;1;2;4;4;4;4;3;3;1;1;1;1';

  -- In "real life" corr always has equal or fewer ";"
  -- corr can contain ";;;;;;4" or NULL as well

  cnt integer;
  res varchar2(2000);

begin
  cnt := REGEXP_COUNT(list,';');
  res := REGEXP_SUBSTR(corr, '[^;]+',1,cnt);

  dbms_output.put_line(res);

  -- output is 4
  -- want it to be 1;1;1;2;4;4;4;4

  -- The result is used to put a substr of list and corr into corresponding cells in 2 tables 
  -- and pass them on to another procedure. This procedure can't handle the number of "cells" in list/corr, 
  -- and my procedure can only retrieve 2 long strings.
end;

Upvotes: 0

Views: 5992

Answers (1)

vav
vav

Reputation: 4684

instr has extra parameter to count a number of matches. Here is an example to get 'gh':

with t (select 'abc;def;gh;ijkl' a_string from dual)
select subtr(a_string, instr(a_string, ';', 1, 2), 
instr(a_string, ';', 1, 3)-instr(a_string, ';', 1, 2)) from t

Aha.. I think, I understand now :)

first step - find number of ';' in a string:

select length(a_string) - length(replace(a_string, ';', '')) cnt from t

then use it to get substring out of second string. If there is no cnt+1 semicolon in a second string, then bring it all, otherwise cut at the next semicolon:

select decode( instr(b_string, ';', 1, cnt+1), 0, b_string, 
substr(b_string, 1, instr(b_string, ';', 1, cnt+1)) from t2

Upvotes: 1

Related Questions