How to select a string between the characters

Question

If my columns(attribute9) contains the field as Pika~Chu~(040)-121-12334~pika78@pika.com the how can I extract the values like

 contact = Pika Chu
 phone_nbr = (040)-121-12334
 email = pika78@pika.com

I had written the code like

regexp_replace(attribute9, '[^()[:digit:]- ]', '')       phone_nbr,
regexp_substr (attribute9,'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}') email,
replace(SUBSTR (attribute9 ,0,(INSTR (attribute9 , '(', -1)) - 1),'~',' ')  contact

Here in phone_nbr I am getting all the digits that were in email(i.e 78) too..How can I extract just between the values (~,~)

regexp_replace(attribute9, '[^()[:digit:]- ]', '') shipto_phone_nbr, regexp_substr (attribute9,'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}') email, replace(SUBSTR (attribute9 ,0,(INSTR (attribute9 , '(', -1)) - 1),'~',' ') shipto_contact, this is the code...for better understanding..please help me — satya vani, Dec 03 '19 at 06:25
Does the order always same in your column? i.e first `contact` then `phonenbr` and then `email` separated by `~`? or it can be in any order? — Arun Palanisamy, Dec 03 '19 at 06:36

score 1 · Answer 1 · answered Dec 03 '19 at 07:22

You can achieve it using a combination of the SUBSTR and INSTR as following:

SQL> SELECT
  2      REPLACE(SUBSTR(STR, 1, INSTR(STR, '~(') - 1), '~', ' ') AS CONTACT,
  3      SUBSTR(STR, INSTR(STR, '~(') + 1, INSTR(STR, '~', - 1) - INSTR(STR, '~(') - 1) AS PHONE_NBR,
  4      SUBSTR(STR, INSTR(STR, '~', - 1) + 1) AS EMAIL
  5  FROM
  6      (SELECT
  7          'Pika~Chu~(040)-121-12334~pika78@pika.com' AS STR
  8       FROM DUAL);

CONTACT  PHONE_NBR       EMAIL
-------- --------------- ---------------
Pika Chu (040)-121-12334 pika78@pika.com

SQL>

Cheers!!

score 0 · Answer 2 · answered Dec 03 '19 at 10:05

You can use only REGEXP to get your output as below.

Select 'Pika~Chu~(040)-121-12334~pika78@pika.com' col,
       regexp_substr('Pika~Chu~(040)-121-12334~pika78@pika.com','(.*?)(~)', 1, 1, NULL, 1 ) ||' ' ||regexp_substr('Pika~Chu~(040)-121-12334~pika78@pika.com','(.*?)(~)', 1, 2, NULL, 1 ) contact,
       regexp_substr('Pika~Chu~(040)-121-12334~pika78@pika.com','(.*?)(~)', 1, 3, NULL, 1 ) phone,
       regexp_substr('Pika~Chu~(040)-121-12334~pika78@pika.com','[^~]+$') email
from dual;

score 0 · Answer 3 · answered Dec 03 '19 at 12:58

You can use regexp_substr():

select (regexp_substr(str, '^[^~]+', 1) || ' ' || regexp_substr(str, '[^~]+', 1, 2)) as contact,
       regexp_substr(str, '[^~]+', 1, 3) as phone,
       regexp_substr(str, '[^~]+', 1, 4) as email
 from (select 'Pika~Chu~(040)-121-12334~pika78@pika.com' AS STR
       from dual
      ) x;

In the above, the only difference in the calls is the last argument, which specifies which component of the string to extract.

Here is a db<>fiddle.

score 0 · Answer 4 · answered Dec 03 '19 at 14:18

Firstly, you should fix the database design as storing multiple values in one column violates first normal form and will cause nothing but headaches.

With that out of the way, use REGEXP_SUBSTR() to split the string into it's components where followed by the delimiter or the end of the line. Doing it this way handles NULL elements while using a consistent regex.

with tbl(id, attribute9) as (
  select 1, 'Pika~Chu~(040)-121-12334~pika78@pika.com' from dual union all
  select 2, 'Drowzee~~(040)-121-23445~dz78@pika.com' from dual
)
SELECT id, 
  regexp_substr(attribute9, '(.*?)(~|$)', 1, 1, NULL, 1) a9_firstname,
  regexp_substr(attribute9, '(.*?)(~|$)', 1, 2, NULL, 1) a9_lastname,
  regexp_substr(attribute9, '(.*?)(~|$)', 1, 3, NULL, 1) a9_phone,
  regexp_substr(attribute9, '(.*?)(~|$)', 1, 4, NULL, 1) a9_email
FROM tbl
order by id;

How to select a string between the characters

4 Answers4