2

I have the below query which works, which gives the nth string corresponding to the key I give in the where clause as names (separator being ##) 

select names from (
select 
    regexp_substr('a##b##c##d##e##f','[^##]+', 1, level) as names,
    rownum as nth
from dual
connect by regexp_substr('a##b##c##d##e##f', '[^##]+', 1, level) is  not    null
)
where nth in (

select nth from (
select 
    regexp_substr('150##200##13##8##51##61','[^##]+', 1, level) as names,
    rownum as nth
from dual
connect by regexp_substr('150##200##13##8##51##61', '[^##]+', 1, level) is  not    null
)
where names = '200'
)

Now, I have a table temp with say 3 columns x,y and z where x has strings like a##b##c##d##e##f, y has 1##2##3##4##5##6 and z will have number like 1.

If I have a rows like

  • a##b##c##d##e##f 150##200##13##8##51##61 200
  • a##b##c##d##e##f 1##2##3##4##5##6 2
  • g##h##i##j##k##l 1##2##3##4##5##99 99

I want outputs like

  • a##b##c##d##e##f 150##200##13##8##51##61 200 b
  • a##b##c##d##e##f 1##2##3##4##5##6 2 b
  • g##h##i##j##k##l 1##2##3##4##5##99 99 l

simply plugging "temp" in place of dual in the above query takes long time as the db has over 50k rows. Any better solution or how do I fix this?

asb
  • 781
  • 2
  • 5
  • 23
  • edited the sample data to make it more closer to the my problem – asb Aug 11 '15 at 14:20
  • Warning:The `regexp_substr` expression of the format '[^,]+' commonly given for parsing a list will not return the expected value if there is a null element in the list and you want that item or one after it. see: https://stackoverflow.com/questions/31464275/split-comma-seperated-values-to-columns/31464699#31464699. – Gary_W Aug 11 '15 at 15:10
  • Oh and add the REGEXP_SUBSTR tag to your post. – Gary_W Aug 11 '15 at 15:10

1 Answers1

1

You may find a plain SQL solution, but I'd prefer to capsule the critical substring functionality in a function. After that the query is trivial

update

with tcols as (
 select  rownum colnum from dual connect by level <= 6 /* (max) number of columns */),
 t2 as (
 select x,y,z, colnum,
 nth_substring(y,'#',colnum) subs
 from regexp_tst, tcols
 )
 select 
 x,y,z, colnum,
 nth_substring(x,'#',colnum) a
 from t2
 where subs = z
 ;

.

 X                Y                         Z A                                                                      
 ---------------- ---------------- ---------- ----
 a##b##c##d##e##f 1##2##3##4##5##6          1 a         
 a##b##c##d##e##f 1##2##3##4##5##6          2 b    
 g##h##i##j##k##l 1##2##3##4##5##6          3 i

The required function is as follows (You may want to adjust the trimming and the repeated delimieter logic)

 create or replace function nth_substring( i_str VARCHAR2, i_del VARCHAR2, i_pos NUMBER) 
 /*
 find n-th substring in delimited string
 i_str input string e.g. a##b##c##d##e##f
 i_del delimiter character
 i_pos position of the strin starting from 1
 */
 return VARCHAR2 is
 BEGIN 
  return rtrim(ltrim(regexp_substr(i_str,'[^'||i_del||']+', 1, i_pos)));
 END;
 /
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • sorry, I guess I have overly simplified the input. What if "Y" is not actually a sequential number? Say it is amounts like 100##150##60##9##8##2000. It will work in the initial query I gave, but in this solution it will not as it expects the position. – asb Aug 11 '15 at 12:40
  • @abs sorry mis-read your query added update. Note also that the regex '[^##]+' is though syntactical correct misleading. It should be '[^#]+' . – Marmite Bomber Aug 12 '15 at 20:45
  • Make sure you test with NULL elements. Always expect the unexpected! – Gary_W Aug 13 '15 at 13:47