Home » SQL & PL/SQL » SQL & PL/SQL » How to get right side value after comma (11g)
How to get right side value after comma [message #631761] Mon, 19 January 2015 10:10 Go to next message
cplusplus1
Messages: 58
Registered: October 2012
Location: usa
Member
select name_full_fomatted from hlth_prof;
returning result:
'Williams MD, Heather Ann'
'Marcada MD, Pamela J'

Doing this to get the value on the left of comma.
ltrim(rtrim(SUBSTR(NAME_FULL_FORMATTED,1,instr(NAME_FULL_FORMATTED,',')-1))) as first_name,

how to get the value after comma which is to right?

Thanks a lot for the helpful info.
Re: How to get right side value after comma [message #631762 is a reply to message #631761] Mon, 19 January 2015 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just use your INSTR call (+1) as second parameter of SUBSTR.

Re: How to get right side value after comma [message #631764 is a reply to message #631761] Mon, 19 January 2015 10:19 Go to previous messageGo to next message
cplusplus1
Messages: 58
Registered: October 2012
Location: usa
Member
Using this, working thanks.

SELECT SUBSTR('abc,def', INSTR('abc,def',',', -1, 1)+1)
FROM DUAL;



Edited by Lalit : Added code tags

[Updated on: Mon, 19 January 2015 10:39] by Moderator

Report message to a moderator

Re: How to get right side value after comma [message #631770 is a reply to message #631764] Mon, 19 January 2015 10:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Overkill:

SELECT SUBSTR('abc,def', INSTR('abc,def',',') + 1)
FROM DUAL;

SUB
---
def

SQL>


SY.
Re: How to get right side value after comma [message #631777 is a reply to message #631770] Mon, 19 January 2015 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, I didn't say anything as it will then work for any number of commas:
SQL> SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',',-1, 1)+1) from dual;
SUB
---
ghi

SQL> SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',') + 1) from dual;
SUBSTR(
-------
def,ghi

Re: How to get right side value after comma [message #631789 is a reply to message #631777] Mon, 19 January 2015 14:13 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Your name should be divided into multiple columns of first_name, middle_name, last_name, title

Then you can do anything you want with the name without parsing it.
Re: How to get right side value after comma [message #631796 is a reply to message #631789] Mon, 19 January 2015 21:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
OP could also use virtual column.
Re: How to get right side value after comma [message #631806 is a reply to message #631796] Tue, 20 January 2015 00:18 Go to previous message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not very efficient.

@OP, I advise you to read Normalization.

Previous Topic: issue with a select query using row_number
Next Topic: Finding SQL Query ...
Goto Forum:
  


Current Time: Mon Mar 18 23:06:02 CDT 2024