I have this string : RC1500ZQ10RC2400ZQ20RC23ZQ3RC2322ZQ22
I need to create a procedure or trigger to split the above string and then insert it as a rows to another tables .
Like This :
RC = NEW ROW .
ZQ = NEW COULMN .
Row 1 RC1500ZQ10 = 1500,10
Row 2 RC2400ZQ20 = 2400,20
Row 3 RC23 ZQ3 = 23,3
Row 4 RC2322ZQ22 = 2322,22
and so on ..
Can anyone help on this ?
Any specific reason why you need procedure or Trigger for this?.
Use REGEXP_SUBSTR with CONNECT BY in a single SQL. You may include the query in your procedure to perform insert by passing the string argument.
'RC(.+?)ZQ' - match anything between RC and next ZQ
'ZQ(.+?)(RC|$)' - match anything between ZQ and next RC or line end
Oracle 11g R2 Schema Setup:
create table t as
select 'RC1500ZQ10RC2400ZQ20RC23ZQ3RC2322ZQ22' as s from dual;
Query 1:
--INSERT INTO yourtarget(rc,zq)
SELECT REGEXP_SUBSTR(s, 'RC(.+?)ZQ', 1, LEVEL, NULL, 1) AS RC,
REGEXP_SUBSTR(s, 'ZQ(.+?)(RC|$)', 1, LEVEL, NULL, 1) AS ZQ
FROM t --or DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(s, 'RC(.+?)ZQ')
| RC | ZQ |
|------|----|
| 1500 | 10 |
| 2400 | 20 |
| 23 | 3 |
| 2322 | 22 |
