This is a starter for you problem, but it is not the complete answer.
Let us say that the column fullname holds the information and you need
to see if at least two of the characters in 'Ahmed' are contained in it.
You could do this...
Select translate(fullname,'Ahmed','*****') from dual ;
The * is any character that will not normally appear in fullname and the
***** is the same number of characters as 'Ahmed'
If fullname is 'William Shakespeare' then the result will be 'Willia*
S*ak*sp*ar*'
You then need to see if there is at least two *. To do this use instr(
'Willia* S*ak*sp*ar*','*',1,2)>0.
So your full statement would be
Select fullname from tablename
Where instr( translate(fullname,'Ahmed','*****') ,'*' ,1 ,2) > 0 ;
This is not eactly what you require (I don't think) because is fullname
= 'Steven' you will still get two * but only the letter e is matching.
Hope it helps anyway. Perhaps someone else can carry it further.
Stephen Weston
(sorry about my gaff with the reverse index - I was momentarily confused
with another language)
-----Original Message-----
From: Agarwal
[mailto:oracledba-ezmlmshield-x62615980.[Email address protected]
Sent: 31 August 2006 18:10
To: LazyDBA Discussion
Subject: Performance hit.
Hi Guys,
I have scenario where I need to make "Like" type of search based on
certain type of combinations on multiple million record table. Like for
full name "Ahmed" I need to find all records which have any two
character of this name as "AM/MD" etc. in any order. As per requirement,
I need to build this query dynamically based on type of input. Due to
various combinations included in search I have getting major performance
hit.
Will anybody share his/her experience for this issue. Thanx in advance.
Regards,
Sanjeev
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page