developer.* Blogs - Oracle - Comments

Thank you!!!!

Guest — Wed, 10 Jan 2007 06:59:26 -0800

Thanks a lot Trevor.Helped a lot.

select u.user_uid, u.login_text, p.profile_name_text from profile p, user u where u.acct_closed_ind = 0 and u.user_uid = p.user_uid and (dbms_lob.instr(clob_field, 'first string to find',1,1) > 0 or dbms_lob.instr(clob_field, 'second string to find',1,1) > 0);

how to find last word in a string

Guest — Wed, 16 Aug 2006 01:59:42 -0700

i have a column that contains customer names...that field may contain a string..if i want to select only the last word from it..how do i do it?
for eg: Column
SANJIV SHARMA
David Nigel
Susan George Edward

so from this i will need to get only
SHARMA
Nigel
Edward

String search

Guest — Tue, 27 Jun 2006 13:06:06 -0700

Hello,

I am using a query to retrive a column data. Its a paragraph. In that I need to look for some specific words(Ex: Hello,there in) and if they are there then I need to get the words next to it and compare with some exisiting text. How do I do that.

Please help.

More qualities?

Mario Van Damme — Mon, 26 Jun 2006 08:03:28 -0700

Apparently I have a quality I wasn't even aware of : "mind reading". I'll see what I can do with that in future :-)

I regularly hear people saying that they don't do their reporting on native Oracle databases, but rather on SQL Server databases. I guess the main drive for this is:
- The business reporting features are good
- Oracle experts are harder to find than SQL Server experts.

Glad to have been of help and good luck with your Oracle project.

Best regards,

Mario.

Oracle, mind-reader

Donna L Davis — Fri, 23 Jun 2006 10:28:54 -0700

The reason I haven't been able to post lately on d.* is because I have been wrestling with this very issue! Thanks, Mario-the-mind-reader. We are going live with an Oracle based third-party system on Monday and I have to make sure several interfaces to other systems don't break. I worked with Oracle several years ago, but since we are a SQL Server shop, I had long ago reused those brain cells. This week I bought "Oracle for Dummies". We are in the process of setting up connectivity from SQL Server 2005 to Oracle so we can dump data (nightly) into a reporting database on SQL Server so that we can have our own familiar playground for creating views, as well as reduce contention for reporting.

Thanks for ur Help

Guest — Wed, 29 Mar 2006 06:48:56 -0800

SELECT DISTINCT USR.*, JOB.*
FROM PROD_PH.PHP_TRG_USR USR,
PROD_PH.PHP_TRG_JOB JOB
WHERE USR.TRG_USR = 'JCIVERA'
AND SUBSTR(JOB.JOB_CLS,1,1) = SUBSTR(USR.JOB_CLS,(INSTR(USR.JOB_CLS,'*')-1),1);

the above select statement very well helped me to built the query

Thanks for the Help provided
Deepak srivatsa

Thanks

Palaniappan — Wed, 30 Nov 2005 11:50:13 -0800

Thanks for the information.I think it would help me

RE: Two Possibilities

Joe Civera — Wed, 02 Nov 2005 15:24:17 -0800

Hi Dan,

Thank you for responding to my question.

Removing the distinct clause provides the same result set in this scenario. It is there only to eliminate redundant rows. The outer join brings back more rows than what I need. Null values are restricted from the tables.

The issue is that INSTR will only find the first occurrence of the pattern (in this case, '*'). There is a second argument I can pass to match a specific occurrence, but in production, I won't ever know what that is.

Regular expressions could provide the solution but are not available in 9i. I'm going to play around with the DECODE function and see if I can come up with something there. Else, I will have to write a function to parse out the data.

Thanks, again!!!

~Joe

Two Possibilities

Daniel Read — Wed, 02 Nov 2005 14:33:13 -0800

Hi Joe,

I have two guesses as to what could be limiting the number of rows you're receiving: one, the DISTINCT clause, and two, the inner join. Have you tried playing with the query to remove DISTINCT and/or change the inner join to a left outer join to see what you get?

It's also possible that null values in either of the JOB_CLS columns could cause problems with the second expressing in your WHERE clause. If either of those columns is nullable you might try wrapping the references in the NVL function.

These are just guesses on my part.

Dan

Multiple string occurrences

Joe Civera — Wed, 02 Nov 2005 11:20:19 -0800

Hello,

What if you want to find multiple occurrences of the same string, but you do not know the amount of occurrences within a given field for a particular row? For example, I have two tables (USR and JOB), and each contains a field named JOB_CLS. The JOB table has three rows, so the values in the JOB_CLS field look like:

C1
C2
M1

The USR table has one row where TRG_USR = 'JCIVERA', and the value in the JOB_CLS field for that row is:

C*,M*

Using the following code:

SELECT DISTINCT USR.*, JOB.*
FROM PROD_PH.PHP_TRG_USR USR,
PROD_PH.PHP_TRG_JOB JOB
WHERE USR.TRG_USR = 'JCIVERA'
AND SUBSTR(JOB.JOB_CLS,1,1) = SUBSTR(USR.JOB_CLS,(INSTR(USR.JOB_CLS,'*')-1),1);

I am only getting a hit on the 1st occurrence of '*', which brings back from the JOB table only the rows where the JOB_CLS field begins with 'C' but I need it to bring back the row where the JOB_CLS field begins with 'M' as well.

Any suggestions? Thanks in advance for your help with this problem.

~Cheers!
Joe