Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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

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
jsmith08
Asked:
jsmith08
  • 2
  • 2
1 Solution
 
PortletPaulCommented:
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
 
jsmith08Author Commented:
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
 
PortletPaulCommented:
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
 
jsmith08Author Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now