Sql stored procedure Like Operator variable

Question

print("select CustomerNo, CustomerName, Address, City, State, Zip,
    Phone, Fax, ContactName, Email  
    from Customers where CustomerName like '%field%'");

Hi all. This is a simple question but I wasn't able to figure since I'm pretty new to tsql and sql in general.

I use the above stored procedure to do search. My question is for '%field%'. What variable do you use or how does it work in tsql? for example, "where Customers = @CustomerNo". how about for wildcard? how do you pass in a variable along with wildcard? I guess i can do "%" + "field" + "%" in the code but is there a way not to do that?

Answer 1

Wildcards are simply part of a string literal, e.g. '%field%' is just a string.

You can concatenate the wildcards onto your string and then use the string:

@Pattern = '%' + @CustomerName + '%';

...WHERE CustomerName LIKE @Pattern

Or else you can write an expression in the SQL involving concatenation:

WHERE CustomerName LIKE '%' + @CustomerName + '%'

There's no other magic solution for this.