Oracle query to pull the middle content from a specific colum

Try this:
select regexp_substr(OriginLocation ,'[^,]+[^,]') from load;

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.

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;

Select allOpen in new window

Undefined function 'regexp_substr' in expression

Even in that case, a simple "substring" as shown above should work...

Hi Alexander, even with :

select substr(OriginLocation, instr(OriginLocation, ',') + 1, instr(OriginLocation, ',', 1, 2) - 3) from load;

Select allOpen 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.

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;

Get rid of the Microsoft ODBC Driver and use the Oracle one.

What version of Oracle is this connecting to?

I have Oracle 9i.

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

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.

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)...

My guess is it an Access Database using Linked Tables.

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

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;

Select allOpen in new window

Let me do one more test.

Thanks a lot for all your help.

This is perfect.

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 https://www.experts-exchange.com/questions/28572777/Oracle-query-to-pull-the-middle-content-from-a-specific-colum.html?anchorAnswerId=40476107#a40476107 as "Assisted solution"?!?! I don't really see any useful information there for you. Actually my very first technical comment on this issue (https://www.experts-exchange.com/questions/28572777/Oracle-query-to-pull-the-middle-content-from-a-specific-colum.html?anchorAnswerId=40475781#a40475781) may (and should) be regarded as a possible solution... In the end, your choices here looks somehow random/arbitrary...

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?

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 ;-)