CODEWITHSUNDEEP
oracle-database
kumar
kumar

Reputation: 89

oracle query to split the [email protected] into columns when ever special char is encountered

Here i have written code but that contains special characters also.But my requirement is ask for user to give a email dynamically and split that email when ever special chars occurs with out special characters i need the out put. 

col1     col2     col3  
------------------

example123  gmail   com

select substr('[email protected]',instr('[email protected]','@'),instr('[email protected]','.')) as col1 ,
     substr('[email protected]',1,instr('[email protected]','@')) as col2,
     substr('[email protected]',instr('[email protected]','.'),length('[email protected]')) as col3 
     from dual;

Upvotes: 0

Views: 118

Answers (1)

Ricardo Arnold
Ricardo Arnold

Reputation: 913

I suggest you to use REGEXP_SUBSTR for splitting strings

Approach 1

In the example below, there is a row for every new word and row and colnumbers are part of the resultset. I suggest you to use this approach since you can not know the numbers of words/colummns beforehand

Query1

with MyString  as
 (          select '[email protected]' Str, 1 rnum from dual
  )
,pivot as (
  Select Rownum Pnum
  From dual
  Connect By Rownum <= 100   
  )
SELECT REGEXP_SUBSTR (ms.Str,'([[:alnum:]])+',1,pv.pnum), ms.rnum, pv.pnum colnum
  FROM MyString ms
      ,pivot pv
where REGEXP_SUBSTR (ms.Str,'([[:alnum:]])+',1,pv.pnum) is not null

Result1

REGEXP_SUBSTR(MS.STR       RNUM     COLNUM
-------------------- ---------- ----------
exapmle123                    1          1
gmail                         1          2
com                           1          3

Approach 2
If you know how many words/columns you'll have, then you can use

Query2

with MyString  as
 (          select '[email protected]' Str, 1 rnum from dual
  )
SELECT REGEXP_SUBSTR (ms.Str,'([[:alnum:]])+',1,1) col1, REGEXP_SUBSTR (ms.Str,'([[:alnum:]])+',1,2) col2, REGEXP_SUBSTR (ms.Str,'([[:alnum:]])+',1,3) col3
  FROM MyString ms

Result2

COL1       COL2  COL
---------- ----- ---
exapmle123 gmail com

Upvotes: 1

Related Questions