CODEWITHSUNDEEP
sqlsql-server
Ali_dotNet
Ali_dotNet

Reputation: 3279

Queries using LIKE wildcards in sql server

I want to perform a small SQL server search in my ASP.NET web project. My database is not big so I think it's better not to use full-text-search.

I want to perform a simple search like this:

select * from mytable where columnA LIKE '%something%'

I can use = in the following way:

select * from mytable where columnA='"+myVariable+"'

but how can I use a variable instead of %something% in the LIKE phrase? Is this correct:

LIKE '"+%myVariable%+"'?

Upvotes: 15

Views: 46725

Answers (8)

RelativitySQL
RelativitySQL

Reputation: 386

If you are worried about sql injection, try something like this instead. It's more complex, but it works and should satisfy security requirements. Let's say someone passed a value into your stored procedure using a parameter called "@searchstring".

DECLARE @searchString nvarchar(100) = 'test',
    @SQL nvarchar(max),
    @foundSearchHit bit,
    @paramdef nvarchar(max) = '@foundSearchHit bit OUTPUT'

SET @searchstring = '%' + @searchString + '%'

SET @SQL = '
SELECT TOP 1 @foundSearchHit = 1
FROM sys.databases WHERE [name] like ' + 
    QUOTENAME(@searchString,'''') 
EXEC sp_executeSQL @SQL, @paramdef, @foundSearchHit = @foundSearchHit OUTPUT

SELECT @foundSearchHit

That should do the trick.

Upvotes: 0

Chad Portman
Chad Portman

Reputation: 1216

In case someone else stumbles into this post like I did. On SSMS 2012 with a SQL 2012 Server back end I was able to use code as follows without issues.

Declare @MyVariable

Set @MyVariable = '%DesiredString%'

Select *

From Table_A

Where Field_A like @MyVariable

Then each time you want to change the Desired String just change it at the Set statement.

I know this post was made prior to 2012 that is why I am mentioning it in case someone with a newer setup looks up this post.

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Use:

where columnA LIKE '%' + myVariable + '%'

Upvotes: 21

Purplegoldfish
Purplegoldfish

Reputation: 5284

I just tried this and found you can do as below:

SELECT * FROM whatever WHERE column LIKE '%'+@var+'%'

Upvotes: 5

YetiSized
YetiSized

Reputation: 21

DECLARE @myVariable varchar(MAX)
SET @myVariable = 'WhatYouAreLookingFor'
SELECT * FROM mytable 
  WHERE columnA LIKE '%' + @myVariable + '%'

Upvotes: 1

Naveed
Naveed

Reputation: 51

Try this query:

select * from tablename where colname like '%' + @varname + '%'

Hope it helps.

Upvotes: 5

Pieter
Pieter

Reputation: 3399

Well you could do something like:

var query = "SELECT * FROM MyTable WHERE columnA LIKE '%" + myVariable + "%'";

Upvotes: 0

sll
sll

Reputation: 62504

WHERE
columnName LIKE '%' + myVarCharVariable +'%'

Upvotes: 9

Related Questions