13

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%+"'?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Ali_dotNet
  • 3,219
  • 10
  • 64
  • 115
  • 4
    Correct but also very bad. Concatenating strings to make SQL statements can leave you wide open to SQL injection. Supposing myVariable has value `';drop table mytable;`? Look at parameterized queries or one of the Linq varieties for interacting with a DB – spender Oct 18 '11 at 08:28
  • @spender : security validation should be done before an query execution anyway so this is not a question of query itself – sll Oct 18 '11 at 08:29
  • 3
    @sll I don't buy it. It's difficult to "security validate" strings that are going to be mixed into SQL statements. It's better simply not to do it. – spender Oct 18 '11 at 08:32
  • dear friends, I found the right answer thanks to your quick replies – Ali_dotNet Oct 18 '11 at 08:34

8 Answers8

18

Use:

where columnA LIKE '%' + myVariable + '%'
Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
  • Why you are used different brackets? – sll Oct 18 '11 at 09:00
  • I think you are talking about the single and double quotes. I used it as an example from your query given in the question, like this: `select * from mytable where columnA='"+myVariable+"'`. So in this query if you want to use `LIKE` then add `%` after the single quote (`'`) and similarly close it. – Aziz Shaikh Oct 18 '11 at 09:08
9
WHERE
columnName LIKE '%' + myVarCharVariable +'%'
sll
  • 61,540
  • 22
  • 104
  • 156
5

Try this query:

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

Hope it helps.

Rostyslav Dzinko
  • 39,424
  • 5
  • 49
  • 62
Naveed
  • 51
  • 1
  • 1
5

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

SELECT * FROM whatever WHERE column LIKE '%'+@var+'%'
chancyWu
  • 14,073
  • 11
  • 62
  • 81
Purplegoldfish
  • 5,268
  • 9
  • 39
  • 59
1
DECLARE @myVariable varchar(MAX)
SET @myVariable = 'WhatYouAreLookingFor'
SELECT * FROM mytable 
  WHERE columnA LIKE '%' + @myVariable + '%'
slfan
  • 8,950
  • 115
  • 65
  • 78
YetiSized
  • 21
  • 3
1

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.

Chad Portman
  • 1,134
  • 4
  • 12
  • 38
0

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.

RelativitySQL
  • 356
  • 3
  • 5
0

Well you could do something like:

var query = "SELECT * FROM MyTable WHERE columnA LIKE '%" + myVariable + "%'";
Pieter
  • 3,339
  • 5
  • 30
  • 63