CODEWITHSUNDEEP
sql-servert-sql
Leonard H. Martin
Leonard H. Martin

Reputation: 2764

SQL Server Express 2008 R2 - Square brackets in text field not returning results

I've created a simple stored procedure with some default values to enable customised searching of my "MachineModel" database.

As part of the WHERE clause I have something like this:

Model.[ModelName] LIKE '%' + ISNULL(@ModelName, Model.[ModelName]) + '%'

which should allow for partial matches. This works for the majority of models, however if no value is supplied for @ModelName (and therefore the ISNULL resolves to Model.[ModelName]) and the underlying field data contains square brackets, such as "Lenny's Test Model [2010]", then those records records aren't returned by the SProc.

This isn't a huge problem as only 4 models (of about 120,000) actually have square brackets in their names so I could easily change them, however I'm curious to know what's going on and what's the best way to solve this.

Cheers,

Lenny.

Upvotes: 0

Views: 868

Answers (2)

jenson-button-event
jenson-button-event

Reputation: 18941

Why are you comparing the model with itself in case of null search parameter? This is expensive.

It is also not great practice to mix ands and ors with SQL Server as its expensive, but could be done:

Where (
 @ModelName is null OR
 (ModelName is not null AND ModelName LIKE '%' + @ModelName + '%')
)

if performance could be a prob, then introducing an if can help, but you do end up duplicating your select

if @ModelName is null 
 select * from ...
else
 select ...
 where ModelName LIKE '%' + @ModelName + '%'

You could, but it will upset your dba, build the sql up in code and use sqlparameters and only add the where when this var is not null

Upvotes: 1

gbn
gbn

Reputation: 432230

You need to replace the leading bracket by [[]

There are 3 characters with meaning in LIKE: % _ [. You don't need to escape the trailing bracket.

For completeness:

Model.[ModelName] LIKE '%' + ISNULL(
    --don't do it the other way around!
    REPLACE(REPLACE(REPLACE(@ModelName, '[', '[[]), '%', '[%]), '_', '[_]),
    Model.[ModelName]) + '%'

Upvotes: 3

Related Questions