Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle 12c SQL - Finding a specific string in a long data type column

Posted on 2016-09-14
4
Medium Priority
?
251 Views
Last Modified: 2016-09-16
Hello Experts,

I need to search for a specific string within a long data type field. The text contained within the field usually looks like:

BOND INFORMATION
 
Arrest Bond Added to Case with:
Action Code: AGGRAVATED DISORDERLY CONDUCT
Arrest Date: 07/21/2016
Bond Status: BOND SET
Status Date: 07/21/2016
Blanket Bond: No
Okay to Apply: No
Bond Type: PERSONAL BOND
 
Charge #1: AGGRAVATED DISORDERLY CONDUCT

I need to find entries that include the text string: Bond Status: BOND SET

...of course when I use basic syntax like:

select d.dkt_text
from dkt d
where d.dkt_text like '%BOND SET%'
and d.dt >= to_date('01-jan-2016');

I get the error message: ORA-00932: inconsistent datatypes: expected NUMBER got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 3 Column: 7

Is it possible to extract a specific string value from a long data type column?
0
Comment
Question by:jsmith08
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41798957
try dbms_lob.instr()
select d.dkt_text
from dkt d
where dbms_lob.instr(d.dkt_text,'BOND SET') > 0
and d.dt >= to_date('01-jan-2016','dd-mmm-yyyy')

Open in new window

ps, ALWAYS use date format masks in the to_date function
0
 

Author Comment

by:jsmith08
ID: 41799170
Thank you. I copied your example above and I received the following error message:

ORA-00997: illegal use of LONG datatype
00997. 00000 -  "illegal use of LONG datatype"
*Cause:    
*Action:
Error at Line: 3 Column: 22

Thanks also for the advice about using a date format mask with the to_date function.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 41799199
The LONG data type is deprecated and you cannot use LIKE with that data type.
 
You also can't use functions designed for CLOB data types
 (my original suggestion assume a CLOB)

You can use TO_LOB() to convert the LONG to CLOB

Then you can use my suggestion.

refs.
https://www.experts-exchange.com/questions/21520838/how-to-find-a-substring-in-a-long-datatype.html

https://www.experts-exchange.com/questions/28268466/find-a-string-in-a-long-column.html#a39576747
0
 

Author Comment

by:jsmith08
ID: 41802320
Thank you for your help and suggestions. I have decided that this will be more involved than is necessary for my assignment. I am going to close the question.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

636 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