Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle query to pull the middle content from a specific colum

Posted on 2014-12-02
19
Medium Priority
?
256 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 78

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 14

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

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 78

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 78

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 78

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 35

Accepted Solution

by:
johnsone earned 1000 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 14

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 78

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 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 200 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 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 14

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 14

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

876 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