Home » SQL & PL/SQL » SQL & PL/SQL » Want to split value based on space if no space want to show null in second column (11g)
Want to split value based on space if no space want to show null in second column [message #631775] Mon, 19 January 2015 11:11 Go to next message
cplusplus1
Messages: 58
Registered: October 2012
Location: usa
Member
I have the following Query: the first part is the name and second part after space is their Title, sometimes there is no title just name, in those cases to show a null in column two. Can you please kindly adivse how to handle in those scenarios as well with one query.


select ltrim(rtrim(SUBSTR('Perez-Pascual MD',1,instr('Perez-Pascual MD',' ')-1))) as Last_name,
ltrim(rtrim(SUBSTR('Perez-Pascual MD', INSTR('Perez-Pascual MD',' ', -1, 1)+1))) as Doctor_Title
from Dual;



select ltrim(rtrim(SUBSTR('Perez-Pascual MD',1,instr('Perez-Pascual MD',' ')-1))) as Last_name,
ltrim(rtrim(SUBSTR('Perez-Pascual', INSTR('Perez-Pascual',' ', -1, 1)+1))) as Doctor_Title
from Dual;


Thanks a lot for the helpful info.
Re: Want to split value based on space if no space want to show null in second column [message #631805 is a reply to message #631775] Tue, 20 January 2015 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use DECODE a CASE to check if INSTR returns 0 or not.

A column should NOT contain multiple values, I advise you to read Normalization.

[Updated on: Tue, 20 January 2015 00:18]

Report message to a moderator

Re: Want to split value based on space if no space want to show null in second column [message #631834 is a reply to message #631805] Tue, 20 January 2015 07:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
No need for DECODE/CASE, just concatenate space at the end in INSTR:

with t as (
           select 'Perez-Pascual MD' str from dual union all
           select 'cplusplus1' from dual
          )
select  substr(str,1,instr(str || ' ',' ') - 1) as Last_name,
        substr(str,instr(str || ' ',' ') + 1) as Doctor_Title
  from  t
/

LAST_NAME        DOCTOR_TITLE
---------------- ----------------
Perez-Pascual    MD
cplusplus1

SQL> 


SY.
Re: Want to split value based on space if no space want to show null in second column [message #631835 is a reply to message #631834] Tue, 20 January 2015 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed. Smile

Re: Want to split value based on space if no space want to show null in second column [message #631916 is a reply to message #631834] Wed, 21 January 2015 18:30 Go to previous message
cplusplus1
Messages: 58
Registered: October 2012
Location: usa
Member
Thanks a lot. That was very helpful.
Previous Topic: How to control the inserting the new records when it cross the limits
Next Topic: Confusion about data length in CLOB type variable in PL/SQL
Goto Forum:
  


Current Time: Tue Mar 19 04:27:42 CDT 2024