Using wildcards with SET

Question

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

Answer 1

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