SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    search exactly the same word in order

    Code:
    data in myTable
    (1) I came as soon as possible.  
    (2) He has trained as a martial arts.  
    (3) As soon as humanly possible  
    (4) He was an American as I knew  
    (5) Say as - As
    I have the above data in myTable, and I made the following CFML and SQL code.
    Code:
    CFML and SQL code
    <cfset keyWord1 = trim(ListFirst(keyWord, '+'))>
    <cfset keyWord2 = trim(Listlast(KeyWord, '+'))>
    
    <cfquery datasource="DS" name="test1">
    	select ID, message
    	from myTable
    	where message like '% #keyWord1# % #keyWord2# %' or
    	      message like '%#keyWord1# % #keyWord2# %' or
    		  message like '% #keyWord1# % #keyWord2#%'
    </cfquery>
    
    <cfoutput query="test1">
       (#DI#)	#message#<br>
    </cfoutput>
    If keyWord is as+as, it produces the following result.
    Code:
    result
    (1) I came as soon as possible. 
    (2) He has trained as a martial arts. 
    (3) As soon as humanly possible 
    (4) He was an American as I knew 
    (5) Say as - As
    I like to produce the following target result.
    Code:
    target result
    (1) I came as soon as possible. 
    (3) As soon as humanly possible  
    (5) Say as - As

    I tried where message rlike '[[:]]#keyword1#[[:>:]] % [[:]]#keyword2#[[:>:]]' but it seems not to work.



    Thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i think what you want is two of the same keyword, right?

    that's what your subject says, that what your examples show, but your syntax uses keyword1 and keyword2

    if they are the same, there's a regexp way to say "two of these" but i don't know what it is, just go look it up in one of the popular regexp sites on the web, e.g. http://regexp.org
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •