SQL Like Operator with CF & Access

Discussion in 'Cold Fusion MX' started by soundcompanies, Apr 3, 2004.

  1. I'm having problems with CF to Access and the Like Operator.

    Code:
    		<cfset addEventArtistCrit = "AND ((t1.eventArtists) Like '" &  #dbWildCard# & toString(#url.artistId#) & #dbWildCard# & "')">
    Code:
    	<cfquery datasource="#dsn#" name="getEvents">
    		SELECT t1.EventId
    			, t1.EventName
    			, t1.VenueId
    			, t1.eventActive
    			, t1.EventDate
    			, t1.PromoterId
    			, t1.eventArtists
    		 FROM tblEvents AS t1 
    		INNER
    		 JOIN tblEvents AS t2
    		 ON (t1.VenueId = t2.VenueId) AND (t1.EventDate = t2.EventDate)
    		WHERE (((t1.EventDate) Between #variable.ODBCstartQueryDate# And #variable.ODBCendQueryDate#)
    		 AND ((t1.eventActive)<>False))
    			 #addEventArtistCrit#
    		GROUP
    		 	BY t1.EventId
    		 	, t1.EventName
    		 	, t1.VenueId
    		 	, t1.eventActive
    		 	, t1.EventDate
    			, t1.PromoterId
    			, t1.eventArtists
    		HAVING (((t1.EventId)=Max([t2].[eventID])));
    	</cfquery>
    
    The artistID that is passed in is numeric but the values stored are comma separated values.

    If I remove the wildcards and the quotes, it will find an exact match, eg.
    Code:
    Like 11
    , but if I have the quotes and the wildcards I get an error, eg.
    Code:
    Like "*11*"
    Here is the error:
    The t1.eventArtists is stored as a string so I can't figure why I'm getting an error.

    Any ideas?

    Thanks,

    Brent
  2. peskypup

    peskypup Perch

    I'll admit that I haven't really read your post closely, but what happens if you try:

    LIKE '%11%'
  3. hatton

    hatton Perch

    That would be my thought as well. I know with SQL Server the % is the wildcard character, not *. I *think* this is the same in Access.
  4. riley

    riley Perch

    Access does not support % as a wildcard character. It does, in fact, support the * character, as well as a few others. From MS Access help:

    * Matches any number of characters. It can be used as the first or last character in the character string. wh* finds what, white, and why
    ? Matches any single alphabetic character. B?ll finds ball, bell, and bill
    [ ] Matches any single character within the brackets. B[ae]ll finds ball and bell but not bill
    ! Matches any character not in the brackets. b[!ae]ll finds bill and bull but not bell
    - Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd
    # Matches any single numeric character. 1#3 finds 103, 113, 123


    riley
  5. Logan

    Logan Perch

    If you're actually in Access, you use the * but I think if you're using Jet to access the Access DB, then you have to use %. I think.
  6. peskypup

    peskypup Perch

    For what it's worth, when you're using an Access DB through ColdFusion, you also follow the SQL convention and use %.
  7. riley

    riley Perch

    Good point Logan. I think you are correct.

    riley
  8. aldog

    aldog Guppy

    well if its just really tripping cause of the quote around the wild card, which you have as a single quote ('), why don't you try doing that as an entity reference? Like &quote;

    I'm not thinking about this one too much cause you probably got a fix by now...and I dont really feel like thinking right now.
  9. alvezinho

    alvezinho Guest

    Can somewhone tell me how can I get a name String whith LIKE. For exemple

    When I search "John Bill Miller"
    I can get "John Bill Sant Miller"

    Thank you
  10. hatton

    hatton Perch

    Easiest way might be to do something along the lines of the following:

    Code:
    SELECT whatever
    FROM wherever
    WHERE 1=1 
     <cfloop list="#form.findname#" delimiters=" " index="thisword">
      AND Name Like ' %#thisword#% '
     </cfloop>
    
    This turns your search string into a space delimited list and loops through it.
  11. GeorgeMike

    GeorgeMike Guest

    LIKE "%WHATEVER" ... pls use double quote, it'll work

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services