Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle query to pull the middle content from a specific colum

Posted on 2014-12-02
19
Medium Priority
?
254 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
[X]
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
  • 6
  • 6
  • 6
  • +1
19 Comments
 
LVL 77

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 77

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 77

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 77

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 77

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 77

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

705 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