Fixed Length String
--------------------
select
SUBSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535',1,INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,1)-1) SEGMENT1
, SUBSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535',INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,1)+1,((INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,2)-1)-INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,1))) SEGMENT2
, SUBSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535',INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,2)+1,((INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,2)-1)-INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,1))) SEGMENT3
, SUBSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535',INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,3)+1,((INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,2)-2)-INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,1))) SEGMENT4
, SUBSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535',INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,4)+1,((INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,2)-1)-INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,1))-1) SEGMENT5
, SUBSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535',INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,5)+1,((INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,2)-1)-INSTR('REB|IUF41501|IUF52402|IUF0233|IUF0524|IUF0535','|',1,1))+1) SEGMENT6
from
DUAL
;
_______________________________________________________________________________________________________________________ NON-Fixed Length String
-------------------------
Method-1:
select
REGEXP_SUBSTR(VAL, '[^|]+', 1, 1) as PART1
, REGEXP_SUBSTR(VAL, '[^|]+', 1, 2) as PART2
, REGEXP_SUBSTR(VAL, '[^|]+', 1, 3) as PART3
, REGEXP_SUBSTR(VAL, '[^|]+', 1, 4) as PART4
, REGEXP_SUBSTR(VAL, '[^|]+', 1, 5) as PART5
, REGEXP_SUBSTR(VAL, '[^|]+', 1, 6) as PART6
from
(
select
'REB|IUF012312311|IUF035453453453422|IUF0333|IUF04444|IUF055555' as val
from
dual
)
;
----------------------------------------------------------------------------------------------------
Method-2: WITH t(val) AS
(
SELECT
replace('REB|IUF01|IUF022|IUF0333|IUF04444|IUF055555','|','!')
FROM
dual
)
, T1 (VAL) as
(
SELECT
'!' || val || '!'
FROM
t
)
SELECT
SUBSTR(val, REGEXP_INSTR(val, '!', 1, 1) + 1, REGEXP_INSTR(val, '!', 1, 1 + 1) - REGEXP_INSTR(val, '!', 1, 1) - 1) Segment_1
, SUBSTR(VAL, REGEXP_INSTR(VAL, '!', 1, 2) + 1, REGEXP_INSTR(VAL, '!', 1, 2 + 1) - REGEXP_INSTR(VAL, '!', 1, 2) - 1) Segment_2
, SUBSTR(VAL, REGEXP_INSTR(VAL, '!', 1, 3) + 1, REGEXP_INSTR(VAL, '!', 1, 3 + 1) - REGEXP_INSTR(VAL, '!', 1, 3) - 1) Segment_3
, SUBSTR(VAL, REGEXP_INSTR(VAL, '!', 1, 4) + 1, REGEXP_INSTR(VAL, '!', 1, 4 + 1) - REGEXP_INSTR(VAL, '!', 1, 4) - 1) Segment_4
, SUBSTR(VAL, REGEXP_INSTR(VAL, '!', 1, 5) + 1, REGEXP_INSTR(VAL, '!', 1, 5 + 1) - REGEXP_INSTR(VAL, '!', 1, 5) - 1) Segment_5
, SUBSTR(VAL, REGEXP_INSTR(VAL, '!', 1, 6) + 1, REGEXP_INSTR(VAL, '!', 1, 6 + 1) - REGEXP_INSTR(VAL, '!', 1, 6) - 1) Segment_6
FROM
t1
;
----------------------------------------------------------------------------------------------------
Method-3 (Displayes NULL
if any null charaters present in the b/w pipes):
select
SUBSTR(VAL,1,INSTR(VAL,'|',1,1)-1) SEG_1
, SUBSTR(VAL,INSTR(VAL,'|',1,1)+1,INSTR(VAL,'|',1,2) - INSTR(VAL,'|',1,1)-1)SEG_2
, SUBSTR(VAL,INSTR(VAL,'|',1,2)+1,INSTR(VAL,'|',1,3) - INSTR(VAL,'|',1,2)-1)SEG_3
, SUBSTR(VAL,INSTR(VAL,'|',1,3)+1,INSTR(VAL,'|',1,4) - INSTR(VAL,'|',1,3)-1)SEG_4
, SUBSTR(VAL,INSTR(VAL,'|',1,4)+1,INSTR(VAL,'|',1,5) - INSTR(VAL,'|',1,4)-1)SEG_5
, SUBSTR(VAL,INSTR(VAL,'|',1,5)+1) seg_6
from
(
SELECT
'REB|IUF012312316511|IUF035453453453422|IUF033hui;3|IUF04444|IUF055555' AS Val
from
dual
)
;
---------------------------------------------------------------------------------------------------------
SELECT
TRIM(SUBSTR(REGEXP_REPLACE(DEBUG,'#|PO',''),REGEXP_INSTR(DEBUG, 'PO'),INSTR(DEBUG,' ',1,1)-1 - REGEXP_INSTR(DEBUG, 'PO')-1)) SEG_1
, TRIM(SUBSTR(REGEXP_REPLACE(DEBUG,'#|PO',''),REGEXP_INSTR(DEBUG, 'PO'),INSTR(DEBUG,' ',1,2)-1 - REGEXP_INSTR(DEBUG, 'PO')-1)) SEG_2
, TRIM(SUBSTR(REGEXP_REPLACE(DEBUG,'#|PO',''),REGEXP_INSTR(DEBUG, 'PO'),INSTR(DEBUG,' ',1,3)-1 - REGEXP_INSTR(DEBUG, 'PO')-1)) SEG_3
FROM
XX_LVWF_APPR_TAB_DEBUG
;
/
select *
from
(
SELECT
SUBSTR(REGEXP_REPLACE(DEBUG,'#|PO',''),REGEXP_INSTR(DEBUG, 'PO'),INSTR(DEBUG,' ',1,1)-1 - REGEXP_INSTR(DEBUG, 'PO')-1) SEG_1
FROM
XX_LVWF_APPR_TAB_DEBUG
UNION ALL
SELECT
TRIM(SUBSTR(REGEXP_REPLACE(DEBUG,'#|PO',''),REGEXP_INSTR(DEBUG, 'PO'),INSTR(DEBUG,' ',1,2)-1 - REGEXP_INSTR(DEBUG, 'PO')-1)) SEG_2
FROM
XX_LVWF_APPR_TAB_DEBUG
UNION ALL
SELECT
TRIM(SUBSTR(REGEXP_REPLACE(DEBUG,'#|PO',''),REGEXP_INSTR(DEBUG, 'PO'),INSTR(DEBUG,' ',1,3)-1 - REGEXP_INSTR(DEBUG, 'PO')-1)) SEG_3
FROM
XX_LVWF_APPR_TAB_DEBUG
)
where
seg_1 is not NULL
;
/
SELECT *
FROM
XX_LVWF_APPR_TAB_DEBUG
;
select
DESCRIPTION
from
GL_JE_LINES
;
INSERT INTO XX_LVWF_APPR_TAB_DEBUG VALUES
('INV#8674767JSDEPOSIT DBCD PO DPO12345678 ASASADSDSS '
)
;
DELETE
FROM
XX_LVWF_APPR_TAB_DEBUG
WHERE
DEBUG = 'ASZXZASSPO12345678 ASASADSDSS PO8674767'
;
SELECT
trim(to_number(regexp_replace(DEBUG, '\D', ''))) OUT_PUT
FROM
XX_LVWF_APPR_TAB_DEBUG
;
SELECT
to_number(regexp_replace(DEBUG, '[^0-9]+', '')) OUT_PUT
FROM
XX_LVWF_APPR_TAB_DEBUG
;
select
TRIM(TRANSLATE(replace(LOWER(debug),'inv','|'),'abcdefghijklmnopqrstuvwxyz()- +/,.#',' ')) OUT_PUT
from
XX_LVWF_APPR_TAB_DEBUG
;
select
replace(TRIM(TRANSLATE(replace(LOWER(debug),'inv','|'),'abcdefghijklmnopqrstuvwxyz()- +/,.#',' ')),' ','') OUT_PUT
from
XX_LVWF_APPR_TAB_DEBUG
;
select
replace((debug),'inv','|')
from
XX_LVWF_APPR_TAB_DEBUG
;
select
length(debug)
from
XX_LVWF_APPR_TAB_DEBUG
;
select
REGEXP_INSTR(replace(DEBUG,'#',''), 'PO')
from
XX_LVWF_APPR_TAB_DEBUG
;
select
TRIM(SUBSTR(REGEXP_REPLACE(LOWER(debug),'#|PO',''),REGEXP_INSTR(debug, 'PO'),length(debug) - INSTR(debug,' ',1,1)-1)) SEG_1
from
XX_LVWF_APPR_TAB_DEBUG
;
select
TRANSLATE(TRIM(SUBSTR(REGEXP_REPLACE(LOWER(debug),'#|PO',''),REGEXP_INSTR(debug, 'PO'),length(debug) - INSTR(debug,' ',1,1)-1)),'abcdefghijklmnopqrstuvwxyz()- +/,.#',' ') SEG_1
from
XX_LVWF_APPR_TAB_DEBUG
;