CODEWITHSUNDEEP
sqloracle-database
My Shopping
My Shopping

Reputation: 19

sql regexp_substr GET STRING BEFORE AND AFTER HYPHEN

I have numbers and their type stored in a single column in oracle DB. Using sql I want to split them and return to user.

For example ABC-1234 ABC is the type 1234 is the number

so far I tried this. Is there is presence of ABC or BN or XY or GF I want to return null

  SELECT 'ABC-1234' TET_NUMBER FROM DUAL
  UNION ALL
  SELECT 'XY - 87686876' TET_NUMBER FROM DUAL
  UNION ALL  
  SELECT ' BN-345345' TET_NUMBER FROM DUAL
  UNION ALL   
    SELECT ' GF - 16666788789789' TET_NUMBER FROM DUAL
  UNION ALL   
    SELECT '987654' TET_NUMBER FROM DUAL

SELECT regexp_substr(TET_NUMBER, '[^-]+', 1, 1) AS PARTBEFORE- 

SELECT regexp_substr(TET_NUMBER, '[^-]+', 1, 1) AS PARTAFTER-

expected output

ABC 1234
XY  87686876
BN  345345
GF  16666788789789

Upvotes: 0

Views: 2204

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

Here you go: the first part selects a "word" (\w), while the second part selects bunch of "digits" (\d). WHERE clause avoids non-alpha rows.

SQL> with test as
  2  (SELECT 'ABC-1234' TET_NUMBER FROM DUAL
  3   UNION ALL
  4   SELECT 'XY - 87686876' TET_NUMBER FROM DUAL
  5   UNION ALL
  6   SELECT ' BN-345345' TET_NUMBER FROM DUAL
  7   UNION ALL
  8   SELECT ' GF - 16666788789789' TET_NUMBER FROM DUAL
  9   UNION ALL
 10   SELECT '987654' TET_NUMBER FROM DUAL
 11  )
 12  select tet_number,
 13    regexp_substr(tet_number, '\w+') first_part,
 14    regexp_substr(tet_number, '\d+') second_part
 15  from test
 16  where regexp_like(tet_number, '[[:alpha:]]+')
 17  ;

TET_NUMBER           FIRST_PART      SECOND_PART
-------------------- --------------- ---------------
ABC-1234             ABC             1234
XY - 87686876        XY              87686876
 BN-345345           BN              345345
 GF - 16666788789789 GF              16666788789789

SQL>

[EDIT: what if input string changes, as stated in the OP's comment]

Nothing much; you'd adjust the regular expression, such as

 <snip>
 14  select
 15    tet_number,
 16    regexp_substr(tet_number, '\w+', 1, 1) first_part,
 17    regexp_substr(tet_number, '\w+', 1, 2) second_part
 18  from test
 19  where regexp_like(tet_number, '[[:alpha:]]+');

TET_NUMBER           FIRST_PART      SECOND_PART
-------------------- --------------- ---------------
ABC-1234             ABC             1234
XY - 87686876        XY              87686876
 BN-345345           BN              345345
 GF - 16666788789789 GF              16666788789789
ABC-1W234            ABC             1W234

Upvotes: 2

Related Questions