macromedia.coldfusion.cfml_general_discussion
[Top] [All Lists]

Re: Like operator in a query

Subject: Re: Like operator in a query
From: "draves"
Date: Thu, 16 Aug 2007 17:51:54 +0000 UTC
Newsgroups: macromedia.coldfusion.cfml_general_discussion
I have had code like some below but it slows thing down because indexes can't 
be used.  Instead for searches that don't have to be exactly up to date I build 
a work file that is in a standardized format nightly but it could be done by 
triggers.  This code is for Oracle.




          where   

 (        length(
          translate(
                UPPER(LTRIM(RTRIM('#Form.StreetName#'))
                             ),
                'A,._%1234567890'
                ,'A')
                ) >= 4 AND 
 (
          V.StNum = '#Form.StreetNumber#'
 <CFIF '#Form.StreetDirection#' NEQ ''>
       and V.stdir = '#Form.StreetDirection#'
 </cfif>
 and (
                   
 V.stName LIKE RTRIM(SUBSTR(

 DECODE(
 SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' 
',1,INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',1,1)-1)
 ,'1','FIRST'
 ,'2','SECOND'
 ,'3','THIRD'
 ,'4','FOURTH'
 ,'5','FIFTH'
 ,'6','SIXTH'
 ,'7','SEVENTH'
 ,'8','EIGHTH'
 ,'9','NINTH'
 ,'10','TENTH'
 ,'11','ELEVENTH'
 ,'12','TWELVTH'
 ,'1ST','FIRST'
 ,'2ND','SECOND'
 ,'3RD','THIRD'
 ,'4TH','FOURTH'
 ,'5TH','FIFTH'
 ,'6TH','SIXTH'
 ,'7TH','SEVENTH'
 ,'8TH','EIGHTH'
 ,'9TH','NINTH'
 ,'10TH','TENTH'
 ,'11TH','ELEVENTH'
 ,'12TH','TWELVTH'
 ,'E','EAST'
 ,'N','NORTH'
 ,'S','SOUTH'
 ,'W','WEST'
 ,'SE','SOUTHEAST'
 ,'SW','SOUTHWEST'
 ,'SAINT','ST'
 ,'MT','MOUNT'
 ,'CT','COURT'
 ,'CORP','CORPORATE'     
 ,SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' 
',1,INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',1,1)-1))
 ||
 DECODE(instr(ltrim(rtrim('#Form.StreetName#')),' ',1,1),0,'',
 ' '||
 SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',1,1)+1,
 INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',-2,1)-INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#')))
 ,'`''_,.%','%%')||' ',' ',1,1))
 ||
 DECODE(
 RTRIM(SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',-2,1)+1))
 ,'AVENUE','AV'
 ,'AVE','AV'
 ,'BOULEVARD','BL'
 ,'BLVD','BL'
 ,'COURT','CT'
 ,'CRT','CT'
 ,'DRIVE','DR'
 ,'LANE','LN'
 ,'PARK','PK'
 ,'PLACE','PL'
 ,'POINT','PT'
 ,'POINTE','PT'
 ,'ROAD','RD'
 ,'SQUARE','SQ'
 ,'STREET','ST'
 ,'STR','ST'
 ,'TRACE','TR'
 ,'TRAIL','TR'
 ,'CIRCLE','CIR'
 ,'CR','CIR'
 ,'EXTENSION','EXT'
 ,'HIGHWAY','HWY'
 ,'HIWAY','HWY'
 ,'HIWY','HWY'
 ,'PIKE','PKE'
 ,'RW','ROW'
 ,'RN','RUN'
 ,'TERRACE','TER'
 ,'WY','WAY'
 
,RTRIM(SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',-2,1)+1)))
 ),1,4))
 ||'%'

 OR 
 V.stName LIKE RTRIM(SUBSTR(

 DECODE(
 SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' 
',1,INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',1,1)-1)
 ,'1','FIRST'
 ,'2','SECOND'
 ,'3','THIRD'
 ,'4','FOURTH'
 ,'5','FIFTH'
 ,'6','SIXTH'
 ,'7','SEVENTH'
 ,'8','EIGHTH'
 ,'9','NINTH'
 ,'10','TENTH'
 ,'11','ELEVENTH'
 ,'12','TWELVTH'
 ,'1ST','FIRST'
 ,'2ND','SECOND'
 ,'3RD','THIRD'
 ,'4TH','FOURTH'
 ,'5TH','FIFTH'
 ,'6TH','SIXTH'
 ,'7TH','SEVENTH'
 ,'8TH','EIGHTH'
 ,'9TH','NINTH'
 ,'10TH','TENTH'
 ,'11TH','ELEVENTH'
 ,'12TH','TWELVTH'
 ,'E','EAST'
 ,'N','NORTH'
 ,'S','SOUTH'
 ,'W','WEST'
 ,'SE','SOUTHEAST'
 ,'SW','SOUTHWEST'
 ,'SAINT','ST'
 ,'MOUNT','MT'
 ,'CT','COURT'
 ,'CORP','CORPORATE'     
 ,SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' 
',1,INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',1,1)-1))
 ||
 DECODE(instr(ltrim(rtrim('#Form.StreetName#')),' ',1,1),0,'',
 ' '||
 SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',1,1)+1,
 INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',-2,1)-INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#')))
 ,'`''_,.%','%%')||' ',' ',1,1))
 ||
 DECODE(
 RTRIM(SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',-2,1)+1))
 ,'AVENUE','AV'
 ,'AVE','AV'
 ,'BOULEVARD','BL'
 ,'BLVD','BL'
 ,'COURT','CT'
 ,'CRT','CT'
 ,'DRIVE','DR'
 ,'LANE','LN'
 ,'PARK','PK'
 ,'PLACE','PL'
 ,'POINT','PT'
 ,'POINTE','PT'
 ,'ROAD','RD'
 ,'SQUARE','SQ'
 ,'STREET','ST'
 ,'STR','ST'
 ,'TRACE','TR'
 ,'TRAIL','TR'
 ,'CIRCLE','CIR'
 ,'CR','CIR'
 ,'EXTENSION','EXT'
 ,'HIGHWAY','HWY'
 ,'HIWAY','HWY'
 ,'HIWY','HWY'
 ,'PIKE','PKE'
 ,'RW','ROW'
 ,'RN','RUN'
 ,'TERRACE','TER'
 ,'WY','WAY'
 
,RTRIM(SUBSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',INSTR(translate(UPPER(LTRIM(RTRIM('#Form.StreetName#'))),'A`''_,.%','A')
 ||' ',' ',-2,1)+1)))
 ),1,4))
 ||'%'     )
                 
         
 ) 
 )



<Prev in Thread] Current Thread [Next in Thread>