OraFAQ Forum: SQL & PL/SQL » Searching string with special characters using INSTR function

Home » SQL & PL/SQL » SQL & PL/SQL » Searching string with special characters using INSTR function (oracle 10g )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Searching string with special characters using INSTR function [message #606817]

Wed, 29 January 2014 10:29

babhuko
Messages: 49
Registered: December 2011
Location: Chennai

Member

Hi all,

My requirement is to search for a string value in a clob data. And if the string is present, am setting flag as Y else N. The problem is, matching string is available in the clob data but its with special characters and that values are skipped out because my search is based on the exact string value. But I need all all the clob records with the matching string. if the string value is there whether with specials characters or extra spaces I need that to be flagged as Y.

Please throw some light on this.

Thanks,
Ram

[Updated on: Wed, 29 January 2014 10:30]

Report message to a moderator

Re: Searching string with special characters using INSTR function [message #606822 is a reply to message #606817]

Wed, 29 January 2014 11:53

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

First remove the characters you want to ignore in both clob and search strings then you just have to search for exact matching:
Something like:

decode(instr(translate(colb_col,'A<ignored characters>','A'),
             translate(search_string'A<ignored characters>','A'),
       0, 'N',
          'Y')

Report message to a moderator

Previous Topic:	Oracle Message - <pck name.proc name> : 301
Next Topic:	How to get back purged data into the tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 13:30:54 CDT 2024