CODEWITHSUNDEEP
oracle-databasesubstr
user2008555
user2008555

Reputation: 21

Oracle SQL substr/instr solution required

I need help with an SQL statement I am trying to run, I have done quite a bit of reading and testing but I cannot get the correct results hence my request here.

I am trying to extract data from a column that has had data concatenated from two sources, I want to seperate the data before & after the join, the join is a ' - ' (hyphen with a space either side), there can be alphanumeric chars before or after that ' - ' and this is the data I need. Just to add to the complexity some rows do not have joined data, i.e. there is no ' - ' and when this is met it is ok to simply extract the whole column value and treat it as the BB side (see first BB example below).

So when I have finished I would like to have seperated out the AA side (before the -) and the BB side (after the -) allowing for the single BB situation.

Looking at the data the following scenarious can occur.

BB<br>
AA - BB<br>
AA-aa - BB<br>
AA - BB-bb-cc<br>
AA-aa - BB-bb-cc<br>

I can get code to work but not consistently for all of the above scenarious - can you suggest the right code or even if there is a better solution which does not impact SQL performance.

Examples I have been trying for AA side:-

WORKS: 

select substr('AA - BB-bb', 0, instr('AA - BB-bb', ' - ', 1, 1)-1) AS A_NAME 
from DUAL;

FAILS (Only gets AA, not AA-aa): 

select substr('AA-aa - BB-bb', 0,instr('AA-aa - BB-bb', ' - ', 1, 1)-1) AS A_NAME
from DUAL;

Examples I have been trying for BB side:-

FAILS: 

select SUBSTR('AA-aa - WHENEHEH', INSTR('AA-aa - WHENEHEH',' - ', -1, 1)+1, 100)
 B_NAME from dual;

thanks, Mark.

Upvotes: 1

Views: 7957

Answers (4)

Rock &#39;em
Rock &#39;em

Reputation: 187

If your seperator is sure gona be a "space on either side of a hypen"

Then you can use CHR() function.

From your try

select substr('AA-aa - BB-bb', 0,instr('AA-aa - BB-bb', ' - ', 1, 1)-1) AS A_NAME
from DUAL;

Use something like this

select substr('AA-aa - BB-bb',0,instr('AA-aa - BB-bb',chr(32))-1) from dual; /* ASCII value for space is chr(32) /*

O/P

AA-aa

select substr('AA-aa - BB-bb',instr('AA-aa - BB-bb',chr(32))+2,10) from dual;

O/P

" - BB"

Upvotes: 1

tbone
tbone

Reputation: 15473

Your condition of a single value show up in second column instead of first is a bit odd, but this should do everything else you require:

with testdata as (
select 'BB'  as input_col, 1 as row_num from dual
union
select 'AA - BB', 2 from dual
union
select 'AA-aa - BB',3 from dual
)
select testdata.row_num,
case when (regexp_instr(testdata.input_col,'(\ -\ )') > 0) then
  regexp_replace(testdata.input_col, '(.*)(\ -\ )(.*)$', '\1')
else null
end output_col1,
regexp_replace(testdata.input_col, '(.*)(\ -\ )(.*)$', '\3') as output_col2
from testdata
order by testdata.row_num;

Edit: I modified the above to add the case check. I admit there is probably a more eloquent way to do this via the regexp_replace itself, but this works too.

Upvotes: 0

Swapna Mohan
Swapna Mohan

Reputation: 175

Here is something that I think will work for you. (I hope I understood your requirement right) I tried the query with all the examples you had mentioned and it works good. 

SELECT REGEXP_SUBSTR ('AA-aa - BB-bb-cc',
                  '[^ - ]+')
      "Right Side",
   SUBSTR ('AA-aa - BB-bb-cc',
             INSTR ('AA-aa - BB-bb-cc',
                    ' - ')
           + 2)
      "Left Side"
  FROM DUAL;

This one works for everything but for BB and as a work around for that you can check if the delimiter exist and if it doesn't you can take the entire value for the right side. 

SELECT    SUBSTR ('AA aa - BB',0,
           INSTR ('AA aa - BB',
                ' - ')
          )
    "Right Side",
SUBSTR('AA aa - BB',INSTR('AA aa - BB',
                  ' - ')+2)
      "Left Side"
 FROM DUAL;

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48111

Assuming that, as shown in your list of scenarios, the pattern '- BB' can be relied on, you can use that to find the split.

WITH
DATA AS (
  SELECT 'BB' text FROM dual
  UNION ALL SELECT 'AA - BB' FROM dual
  UNION ALL SELECT 'AA-aa - BB' FROM dual
  UNION ALL SELECT 'AA - BB-bb-cc' FROM dual
  UNION ALL SELECT 'AA-aa - BB-bb-cc' FROM dual
  ),
break AS (
  SELECT text, instr(text, '- BB') breakpos  FROM DATA
)
SELECT
  text,
  CASE WHEN breakpos = 0 THEN NULL ELSE substr( text, 1, breakpos-1 ) END aa_side,
  case when breakpos = 0 then text else substr( text, breakpos+2) end bb_side
FROM break
;

Upvotes: 0

Related Questions