Using wildcards with SET

user_1184100 0
Viewed 32

Table

productName   description

+++++++++++++++++++++++++

OpenIDM       Platform for building enterprise provisioning solutions

OpenDM        Full-featured access management

OpenDJ        Robust LDAP server for Java

In the above table when I run below query I get the first two records as expected

DECLARE @searchInput varchar
SET @searchInput = 'dm'
SELECT * FROM ForgeRock
WHERE productName like '%DM%'

Result OpenIDM Platform for building enterprise provisioning solutions OpenDM Full-featured access management

But when I run the below query, pattern matching doesn't seem to work properly and I get all the records.

DECLARE @searchInput varchar
SET @searchInput = 'dm'
SELECT * FROM ForgeRock
WHERE productName like '%'+@searchInput+'%'

http://sqlfiddle.com/#!18/6e9af/8

sql-server
3 Answers

You didn't declare the size of the varchar string. It default to 1. Do a SELECT on the @searchInput and see for yourself

DECLARE @searchInput varchar
SET @searchInput = 'dm'

SELECT @searchInput
user_1856470

Yeah like @Lamu and @Squirrel said, you should declare the length of your search string e.g :

DECLARE @searchInput varchar(50)
user_101675740

When You are declaring a variable without any size os by default it will take as 1. For Example You have decalre 

DECLARE @searchInput varchar(10)
SET @searchInput = 'dm'

IF Declare without size than it take only d and d is Present in All rows so It is returning all rows.

user_102781580
Related