Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle query to pull the middle content from a specific colum

Posted on 2014-12-02
19
247 Views
Last Modified: 2014-12-03
Hi

I have SQL that i pull every day:
select OriginLocation from load;

Open in new window


The problem i have is that the cell content is:
4,201011222100,20139

In the cell, i have 2 commas.

I want to query only what in the middle of the 2 commas.

Ex:
If i have 4,201011222100,20139 in the cell, i want to only pull 201011222100
If i have 3,03,15634 in the cell, i want to only pull 03

The number of chars can vary between the 2 commas.

How can i do this?

Thanks
0
Comment
Question by:Wilder1626
  • 6
  • 6
  • 6
  • +1
19 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40475767
Try this:
select regexp_substr(OriginLocation ,'[^,]+[^,]') from load;
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40475780
I just found out this this is an access dbase connected to Oracle SQL Developper tool.

So when i use : select regexp_substr(OriginLocation ,'[^,]+[^,]') from load;  i have this error:
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'regexp_substr' in expression.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40475781
Yes, try to use built-in regexp function when possible.
But if you're running Oracle 9i and you don't want to write your own regexp-... functions, you also could use something like this:
select substr(OriginLocation, instr(OriginLocation, ',') + 1, instr(OriginLocation, ',', 1, 2) - 3) from load;

Open in new window

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40475783
Undefined function 'regexp_substr' in expression
Even in that case, a simple "substring" as shown above should work...
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40475787
Hi Alexander, even with :
select substr(OriginLocation, instr(OriginLocation, ',') + 1, instr(OriginLocation, ',', 1, 2) - 3) from load;

Open in new window


I still get the error: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'substr' in expression.

I have Access 365 on Windows 8.1, if this can help.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40475789
Assuming the number in the front might be more than 1 number try this:

select substr(OriginLocation,instr(OriginLocation,',')+1,instr(OriginLocation,',',1,2)-instr(OriginLocation,',')-1) from load;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40475791
Get rid of the Microsoft ODBC Driver and use the Oracle one.

What version of Oracle is this connecting to?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40475793
I have Oracle 9i.

How to I get rid of Microsoft ODBC Driver so that i can use the Oracle one?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40475799
Install the Oracle ODBC driver using the same installation where you installed the Oracle Client.  ODBC isn't installed by default with most Oracle Client installs.  Just rerun setup.exe and select it.  You should be able to install it into the same ORACLE_HOME as the current client.

Then just create the DSN using the Oracle driver instead of the Microsoft one.
0
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 40475848
I am confused a bit.  Post ID: 40475780 indicates this is an Access database, not an Oracle database.

If that is the case, then  you need to use MID, not SUBSTR.

I really don't know Access, but this would be my best guess based on a couple of searches:

mid(OriginLocation, instr(1, OriginLocation, ',') + 1, instr(instr(1, OriginLocation, ',')+1, OriginLocation, ',') - instr(1, OriginLocation, ',') - 1)

These are the reference pages I used:

MID -> http://www.techonthenet.com/access/functions/string/mid.php
INSTR -> http://www.techonthenet.com/access/functions/string/instr.php
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40475855
I have Access 365 on Windows 8.1, if this can help.
Obviously Oracle's not at home ;-)
So, yes, replace "substr" with the corresponding "mid" (whereas these constructs exists in virtually every language)...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40475882
My guess is it an Access Database using Linked Tables.

If the query is a pass through query, Oracle syntax applies.
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 50 total points
ID: 40476107
slightwv: Agreed, but I think, guessing is not an option here ;-)
The asker has to provide the information what DB is running on which tool(s)...

I still get the error: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'substr' in expression.

I have Access 365 on Windows 8.1, if this can help.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 40476166
Try this for a straight Access query (Tested with Access 2007):
select mid(OriginLocation, instr(1,OriginLocation,',')+1, instr(instr(1,OriginLocation,',')+1,OriginLocation,',')-instr(1,OriginLocation,',')-1) from load;

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40476173
Looks like it's working with:
select mid(OriginLocation, instr(1,OriginLocation,',')+1, instr(instr(1,OriginLocation,',')+1,OriginLocation,',')-instr(1,OriginLocation,',')-1) from load;

Open in new window


Let me do one more test.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40477014
Thanks a lot for all your help.

This is perfect.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40477395
I have absolutely no clue how / based on what pattern you distributed the points and how exactly you've chosen the right answers... ??!!!!
Would you please be so kind to tell me/us, why you marked my comment http://www.experts-exchange.com/Database/Oracle/Q_28572777.html#a40476107 as "Assisted solution"?!?! I don't really see any useful information there for you. Actually my very first technical comment on this issue (http://www.experts-exchange.com/Database/Oracle/Q_28572777.html#a40475781) may (and should) be regarded as a possible solution... In the end, your choices here looks somehow random/arbitrary...
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40477643
Hi Alexander.

sorry, I should of select your post: ID: 40475781.

As for 250 points for post: ID: 40475848 and 250 points for ID: 40476166, both have the exact same SQL example. It is just that ID: 40475848 was given first.

And yes, since you also helped, I gave you something also. In the past, some other people were sad that I did not gave any point since they have help.

So I don't think that I did something wrong here except that I should of select your post ID: 40475781.

If the point attribution should be given by the first or the only one that provided the best solution, then fine, I will do this going forward. But this would need to be clear on how the forum work.

Can you tell me how does the points should be distribute?
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40478132
Actually you're more or less free in distributing the points... So, if those comments were the first to help you, go on then, I don't mind & I don't bother ;-)

Kind regards,
Alex

P.S.: The entire EE platform (including community support & help) is searchable, and there are quite a few guidelines out here on how to accept what comment/solution and how to distribute points with what grade etc....

P.P.S.: It just didn't make sense to me the way you closed this one. I am really NOT after the point ;-)
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question