Using like in a where clause with user input

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using like in a where clause with user input

Author

Topic

jodders
Starting Member

41 Posts

Posted - 2012-01-31 : 07:55:03

Hi there,

Having some trouble with my report which asks a user to enter a word and in searches for that word in a particular column.

So far I have this:


declare @words char(50)  
set @words = 'money'    -- Enter Group Code

select 
...
from
...
where
D.REMARKS LIKE '%@words%'

Am i doing this right?

My results are not bringing back anything, I am doing something obviously wrong which I can't figure out.

cheers!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-31 : 07:59:14

[code]D.REMARKS LIKE '%'+@words+'%'[/code]This approach is susceptive to SQL injection though, unless you take preventive measures.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-31 : 07:59:20

I prefer using CHARINDEX in a case like this.
select
...
from
...
where
CHARINDEX(@words, D.REMARKS) > 0

Duane.

jodders
Starting Member

41 Posts

Posted - 2012-01-31 : 08:27:54

Thanks for the quick response, I think SQL does not like's the @sign as it still does not bring back any information.

It is rather odd. If i remove the Declare part and add the word into where clause, it brings back the results.

WHERE D.REMARKS LIKE '%money%'

Maybe my declaration at the top is wrong?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-31 : 08:36:11

The Charindex option should work regardless, it also is less of a "Hack"

Duane.

jodders
Starting Member

41 Posts

Posted - 2012-01-31 : 08:55:21

Thanks Duane for introducing me to the "Charindex", used it in another piece of code and it works well.

I think my code that I have put together is wrong then. Below is my sample code.

can you spot anything wrong?



------Word-----------------------------------
declare @words char(50)
set @words = 'money'   
----------------------------------------------------------------------------------------------------------------------------
SELECT 	
	C.TRUST_NUM, 
    C.TRUST_NAME, 
     B.CLIENT_CODE, 
     B.CLIENT_NAME, 
	C.BANK_CODE, 
    D.TC_CODE,
     D.REMARKS
FROM 
	HBM_MATTER A
	JOIN HBM_CLIENT B ON B.CLIENT_UNO = A.CLIENT_UNO
	JOIN TRM_TRUST C ON C.MATTER_UNO = A.MATTER_UNO
	JOIN ACT_TRAN_TRUST D ON D.TRUST_UNO = C.TRUST_UNO
	JOIN TBL_CURR_RATE H ON H.CURRENCY_CODE = D.TC_CODE               
	

WHERE CHARINDEX(@words,D.REMARKS) > 0

GROUP BY
C.TRUST_NUM, 
    C.TRUST_NAME, 
     B.CLIENT_CODE, 
     B.CLIENT_NAME, 
	C.BANK_CODE, 
    D.TC_CODE,
     D.REMARKS

	
ORDER BY
B.CLIENT_CODE,C.BANK_CODE

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-31 : 08:58:32

Why are you using GROUP BY? there is no aggregation happening in your query.

Duane.

jodders
Starting Member

41 Posts

Posted - 2012-01-31 : 09:02:09

I was trying out a few other things and left it in there by mistake.Even after removing it, it doesn't work.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-31 : 09:06:58

Possibly the join criteria.... does anything get returned if your where clause is commented out?

Duane.

jodders
Starting Member

41 Posts

Posted - 2012-01-31 : 09:16:08

Appreciate the effort you are making Duane,i don't think it's the joins.

maybe it's me but I have tried a very simple select statement and the CHARINDEX does not return anything.


Doesn't work
declare @words varchar(50)
set @words = 'bank'   
----------------------------
select * from client where CHARINDEX(@words,clientname) > 0

This works:
select * from client where CHARINDEX('bank',clientname) > 0

jodders
Starting Member

41 Posts

Posted - 2012-01-31 : 11:54:23

i fixed it, we were along the right tracks,just missing an extra ' '

CHARINDEX(''+@word+'',D.REMARKS) > 0

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-31 : 12:14:42

it's...so..dynamic

Water closet

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-31 : 14:08:54

What if your variable was a varchar instead of a char? I can't test it myself as I am sucking on a beer watching tv and browsing on the iPad..... Is there a SSMS apps for this.

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-02-01 : 03:15:46

Ahhh no need to hack it. A Char value pads spaces at the end of it, so it is in essence not searching for what you think it is. Use Varchar for @Words instead that way it wont be padded with spaces.

See the results you get from the below query :

declare @C CHAR(10)
declare @V VARCHAR(10)
DECLARE @Search VARCHAR(50)

SET @Search = 'ASDFGVVVVGGTTYY'
SET @C = 'VV'
SET @V = 'VV'
SELECT
@C + '*****',
@V + '******',
CHARINDEX(@C, @SEARCH),
CHARINDEX(@V, @Search)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Results:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
VV ***** VV****** 0 6

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources