Home » SQL & PL/SQL » SQL & PL/SQL » Select distinct rows before special character (Oracle 11g)
Select distinct rows before special character [message #625729] Mon, 13 October 2014 12:13 Go to next message
abhayman
Messages: 37
Registered: August 2011
Location: CA
Member
Hi,

I want to select all distinct rows from a table before space.

-------------------------------------
ID |||| Product
-------------------------------------
1 |||| MNO (XYZ)
2 |||| MNO (XYZ A)
3 |||| MNO (XYZ X)
4 |||| MNO (ABC)

I want the output to be
XYZ
ABC

I tried various distinct using

SELECT SUBSTR(t.product, 0, INSTR(t.product, ' ')-1) AS output
FROM employee t
but this gives only result as MNO.

Any help would be of great assistance.

Regards.
Re: Select distinct rows before special character [message #625730 is a reply to message #625729] Mon, 13 October 2014 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/520454/#msg_520454
Re: Select distinct rows before special character [message #625732 is a reply to message #625730] Mon, 13 October 2014 12:29 Go to previous messageGo to next message
abhayman
Messages: 37
Registered: August 2011
Location: CA
Member
Ignore the post I was able to find the answer .

select t.id , regexp_substr(t.product,'[^ ]+',1,2)
from employee t
Re: Select distinct rows before special character [message #625734 is a reply to message #625732] Mon, 13 October 2014 12:51 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I don't think REGEX would be faster than the old SUBSTR/INSTR approach. It would be much resource consuming.
Re: Select distinct rows before special character [message #625742 is a reply to message #625729] Mon, 13 October 2014 13:53 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Before your next topic remember:

BlackSwan wrote on Tue, 23 August 2011 00:31
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


BlackSwan wrote on Tue, 23 August 2011 00:50
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/.
...


Michel Cadot wrote on Tue, 23 August 2011 07:55
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


Michel Cadot wrote on Fri, 02 September 2011 07:46
Michel Cadot wrote on Tue, 23 August 2011 07:55
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel


If you make no effort to post and help us to help you, I see no reason to spend some time to help you.

Regards
Michel


BlackSwan wrote on Tue, 11 October 2011 02:20
...
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311


BlackSwan wrote on Mon, 31 October 2011 23:15
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
...


BlackSwan wrote on Thu, 07 November 2013 21:26
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.


And feedback to and thank people who help you.
Previous Topic: Merge data to a table based on criteria
Next Topic: The optimal approach to this search
Goto Forum:
  


Current Time: Fri Mar 29 01:34:23 CDT 2024