id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
3998	single char wildcard search issue	Matt Boersma	Adrian Holovaty	"I'm trying to implement a search feature in Django where a user can enter something like[[BR]]

 
PC-3[[BR]]

 
and the search will return matches with or without punctuation, case insensitive[[BR]]

 
results:   PC-3, pc3, BxPC3, Pc 3[[BR]]

 
I've figured out how to do this so I'm getting all of the results above, but the only thing that's missing is the ""Pc 3"" result. It seems that when I do an Oracle search using ""_"" as a single-char wildcard, it doesn't consider a ""space"" to match that wildcard.

The query that is generated:

{{{

SELECT DISTINCT ""CRYOINV_WELL"".""CELL_LINE""
 FROM ""CRYOINV_WELL""
 WHERE ((UPPER(""CRYOINV_WELL"".""CELL_LINE"") LIKE UPPER('%PC-3%')
 OR UPPER(""CRYOINV_WELL"".""CELL_LINE"") LIKE UPPER('%PC\\_3%')
 OR UPPER(""CRYOINV_WELL"".""CELL_LINE"") LIKE UPPER('%PC3%')));
}}}


The problem is the double backslash that's put into the query on line 4.

Here's some code snippets:

models.py:


{{{
class Well(models.Model):
  box = models.ForeignKey(Box, edit_inline=models.TABULAR, num_in_admin=18)
  well_name = models.CharField(maxlength=20, core=True)
  cell_line = models.CharField(maxlength=250, null=True, blank=True)
}}}


views.py:


{{{
def search(request):

  WellFormClass = forms.form_for_model(Well)
  WellFormClass.base_fields['box'].widget = widgets.HiddenInput()
  WellFormClass.base_fields['well_name'].widget = widgets.HiddenInput()

  if request.GET:
    query = request.GET.get(""cell_line"", """")
    # Do a ""liberal"" search first
    # Search for:    PC-3
    # Returned results:   PC-3, pc3, BxPC3, pc 3
    
    # replace any punctuation in the query with a '_'
    # *** for some reason ignoring the space?
    punc_trans = string.maketrans(' -:;', '____')
    modQuery1 = query.translate(punc_trans)
    # parse out any punctuation at all
    no_trans = string.maketrans('', '')
    modQuery2 = query.translate(no_trans, '-:; ')
    # now search for any of these combinations
    results = Well.objects.filter(Q(cell_line__icontains=query) |
                                  Q(cell_line__icontains=modQuery1) |
                                  Q(cell_line__icontains=modQuery2)).values(""cell_line"").distinct()

    results = list(results)
    return render_to_response('cryoInv/cell_line_search_results.html',
                              {""results"": results,""query"": query})
  else:
    form = WellFormClass()

  return render_to_response('cryoInv/search.html', {'form': form})      

}}}
"		closed	Database layer (models, ORM)	dev		wontfix	sql		Accepted	0	0	0	0	0	0