Home » SQL & PL/SQL » SQL & PL/SQL » Searching excluding certain characters
Searching excluding certain characters [message #236557] Thu, 10 May 2007 04:41 Go to next message
Messages: 1
Registered: May 2007
Junior Member
I am extremely new here. I know this must have been done 100s of times before as its an obvious need for end users and I have my thoughts on how to do it - but I want to ensure that I implement the most effective way.

The forms screen we use allows the user the enter search criteria for a company name.

Assume the company name is A.B. Company Inc. and this is how it is stored

Right now the code will only return this value if they filter on A. or A%B etc.

I want them to get A.B. Company Inc. even if they search on AB.
Similar for other characters like - " ' - I can get a list from the users.

I thought of creating a new column to be used for searches that would store company names after transforming them using the statement below that I picked up from another post; or this translation could be done on the fly - but I'm concerned about performance. Is this a valid concern ?

Once I have the transformed string I can do the comparison with the search criteria.

Is there any other way to do this that is efficient ?

Select translate(upper(trim(col1)),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ ()_+}{":?><`-=]['''';/.,','1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ')
Re: Searching excluding certain characters [message #236559 is a reply to message #236557] Thu, 10 May 2007 04:43 Go to previous message
Messages: 7880
Registered: March 2000
Senior Member

Welcome to the forum.
You might take a look at the soundex function. An alternative might be using an Oracle Text index.
Previous Topic: Duplicate Row Containing a Long Raw Field.
Next Topic: how to ignore whitespace in the result of a query
Goto Forum:

Current Time: Tue Jul 25 22:11:10 CDT 2017

Total time taken to generate the page: 0.13872 seconds