Substring Search in an Oracle CLOB
This entry falls under the "Something New I Learned Today" heading.
I needed to obtain a list of active users who had a particular string within a CLOB field in an auxillary table associated back to their account via a foreign key. You can't do "LIKE" statements against a CLOB, and we don't have Intermedia operational on the boxes where the query needed to run so I couldn't run a "contains" against an index. I say "boxes" because we have two distinct user databases -- one running Oracle 9i and another on 8i. So whatever solution I came up with had to play in both environments. Our DBA mentioned there was a package resident in both called DBMS_LOB which exposed methods for working with LOBs. I saw that one of these was InStr() and hoped that it would allow me to specify the substring I was concerned with, and perhaps return the index where the substring started within the CLOB.
Sometimes there IS reason in the universe. I was able to do this using the following PL/SQL (names have been changed to protect the innocent):
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
);
Obviously you could stick as many substrings in there as you want using additional ORs. The DBMS_LOB package has all kinds of interesting functions for dealing LOBs. We could have even run a script to update/remove the offending strings automatically. But we opted for safety and will instead have the Customer Support team call the users so they can be educated.
Two Possibilities
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
RE: Two Possibilities
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
Thanks
Thanks for the information.I think it would help me
Thanks for ur Help
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
String search
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.
how to find last word in a string
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
Thank you!!!!
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);
Multiple string occurrences
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