Oracle SQL Select Split Results

Question

I am trying to create a SQL statement to return only a portion of a column result. I have looked into SubString and InString, but am having a lot of trouble returning the values I need.

Example table data is:

RM123 - N60
RM123 - J44
RM123 - Q24
RM123 - U55-R07 - 1
RM123 - U54-R06 - 1
RM123 - R47-R11 - 1

I need to only return the data after the second space and before the third (could contain dashes, will NOT contain spaces in the results needed):

N60
J44
Q24
U55-R07
U54-R06
R47-R11

Equivalent of what I need in awk...

$ awk '{print $3}' /tmp/sql.out
N60
J44
Q24
U55-R07
U54-R06
R47-R11

Any help would be appreciated!

The second space in the line overall, or just here (ie what about '_-', where '_' is standing in for space)? What have you tried so far? — Clockwork-Muse
– Clockwork-Muse, Commented Aug 14, 2012 at 22:07
I'm not sure I fully understand what you are asking, but there should be no underscores in the column data. Using spaces as a delimiter, I need the third column (between the second space third space). I'm more or less lost, so I don't have much to share. Here is all I have started with using information from other questions I see posted: select SUBSTR(Serial_Number, 1, INSTR(Serial_Number, ' ')-1) AS ouput. This however only provides "RM123" in this case, I can't sort out how to get the position of the second space, and length of the data between which is what substr seems to require. — nvrmore100
– nvrmore100, Commented Aug 14, 2012 at 22:22
Does INSTR() allow you to specify a starting index (#hint#). Also, you may have better luck wrapping things in some layers of CTEs, allowing you to only have to calculate function values once. — Clockwork-Muse
– Clockwork-Muse, Commented Aug 14, 2012 at 22:32

nvrmore100 · Accepted Answer · 2012-08-14 22:37:11Z

1

Found a solution to my own question, so I'm posting it for others. You can use regexp_substr to solve this. Here an example:

select regexp_substr(columnname,'[^ ]+', 1, 3)

This will return the third column using spaces as delimiters. All props goes to this page which gave me the hint:

http://andrewfraserdba.com/2011/07/13/split-space-delimited-string-with-regexp-sql/

answered Aug 14, 2012 at 22:37

nvrmore100

271 gold badge2 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Select Split Results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related