Syntax - SQL Server Forums

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

Syntax

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-01-24 : 11:37:31

OK I have this

lets say 'Test String' is in a str..
@VarStr = 'Test String'
[CODE]
SELECT * from tab where name like @VarStr
or
SELECT * from tab where name like '@VarStr%'
[/CODE]
trouble is 2nd doesn't work
This wont work either ..
[CODE]
select * from email where send_from like @VarStr + '%'
[/CODE]

what is the syntax if i wanna add a % to a variable in the like clause ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-01-24 : 11:46:38

What makes you say the last one doesn't work?


declare @email table (send_from varchar(50))
declare @VarStr varchar(50)

insert into @email
select 'test string'
union select 'test string 1'
union select 'test string 2'

set @VarStr = 'Test String'

select * from @email where send_from like @VarStr + '%'

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-01-25 : 03:43:36

because i get different values for
:
[CODE]
declare @VarStr as varchar
@VarStr = 'Test String'
select count(*) from email where send_from like @VarStr + '%'
[/CODE]
and
[CODE]
select count(*) from email where send_from like 'Test String'
[/CODE]

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-25 : 03:59:03

You will surely get different values for
declare @VarStr as varchar
@VarStr = 'Test String'
select count(*) from email where send_from like @VarStr + '%'

select count(*) from email where send_from like 'Test String'

In second statement we are not putting '%', so it searches for exact match i.e. 'Test String'. I got following output:
(3 row(s) affected)
send_from
--------------------------------------------------
test string
test string 1
test string 2

(3 row(s) affected)

send_from
--------------------------------------------------
test string

(1 row(s) affected)

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-01-29 : 05:51:04

Sorry i meant i got diff values when i included the % !

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-01-29 : 06:01:56

Can you post the data you got differences on?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 06:27:52

Try this

SELECT		send_from,
		COUNT(*)
FROM		email
WHERE		send_from LIKE @VarStr + '%'
GROUP BY	send_from
ORDER BY	send_from

E 12°55'05.25"
N 56°04'39.16"

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-01-29 : 06:59:38

Well not really .. i can't post DB data ..
but i can tell you when i ran the LIKE @VarStr + '%' QRY ..

it was returning strings that were like the 1st few chars of VarStr ..
... shit i know why !!!

i think i declared it as a varhcar (10) ... so it was being truncated !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources