• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2806
  • Last Modified:

find a string in a long column

Experts -

How can I search for a string in a Long column like below?

select count(*) from dba_views where text like ('%@ABCPRD%')

I getting below error when I execute the above sql.

ORA-00932: inconsistent datatypes

  • 3
  • 2
2 Solutions
Ess KayEntrapenuerCommented:
you need to specify the column name instead of text

also, LONG is a number, should never have a string in it. you can convert to string
slightwv (䄆 Netminder) Commented:
You cannot use a LIKE with a LONG.

The best way I can find is creating your own function, convert the LONG to a CLOB and search that.

There is an example here:

Now if dba_views was just for example and you have a table with a LONG you want to search you can also create a Text index on a LONG and use CONTAINS to look for text.
slightwv (䄆 Netminder) Commented:
>>also, LONG is a number, should never have a string in it. you can convert to string

Very incorrect.  LONGs are for large amounts of Text data before CLOBs.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

sventhanAuthor Commented:

I getting the same error
Ess KayEntrapenuerCommented:
yea sorry, i mixed it up with programming and by the time i realized it i couldnt retract that statement
Ess KayEntrapenuerCommented:
here is a method to convert long to varchar. then you simply search the varchars

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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