CODEWITHSUNDEEP
sql-server
user1184100
user1184100

Reputation: 6894

Using wildcards with SET

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

Upvotes: 3

Views: 36

Answers (3)

Bhargav J Patel
Bhargav J Patel

Reputation: 166

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.

Upvotes: 0

Noldy Nayoan
Noldy Nayoan

Reputation: 127

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

DECLARE @searchInput varchar(50)

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 4

Related Questions