Oracle query to pull the middle content from a specific colum

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
LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Try this:
select regexp_substr(OriginLocation ,'[^,]+[^,]') from load;
0
Wilder1626Author Commented:
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
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Alexander Eßer [Alex140181]Software DeveloperCommented:
Undefined function 'regexp_substr' in expression
Even in that case, a simple "substring" as shown above should work...
0
Wilder1626Author Commented:
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
slightwv (䄆 Netminder) Commented:
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
slightwv (䄆 Netminder) Commented:
Get rid of the Microsoft ODBC Driver and use the Oracle one.

What version of Oracle is this connecting to?
0
Wilder1626Author Commented:
I have Oracle 9i.

How to I get rid of Microsoft ODBC Driver so that i can use the Oracle one?
0
slightwv (䄆 Netminder) Commented:
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
johnsoneSenior Oracle DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
slightwv (䄆 Netminder) Commented:
My guess is it an Access Database using Linked Tables.

If the query is a pass through query, Oracle syntax applies.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
slightwv (䄆 Netminder) Commented:
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
Wilder1626Author Commented:
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
Wilder1626Author Commented:
Thanks a lot for all your help.

This is perfect.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
Wilder1626Author Commented:
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
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.