Solved

Oracle query to pull the middle content from a specific colum

Posted on 2014-12-02
19
235 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now