Like @variable + '%' 2

[sera]

I am getting incorrect results from my query.

If I write the query like this I get too many results:

set @var = 'matchstring'
select * from table where aval Like @var + '%'

If I write the query like this I get the correct results:

select * from table where aval Like 'matchstring%'

....I am using SQLServer 2005.

Anyone have an answer as to why?

Sera

 

[bborissov]

no way
Are you sure you didn't use:

set @var = 'matchstring'
select * from table where aval Like '%' + @var + '%'

when you use variable?

Could you create a repro code?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[sera]

Yes I am sure!

Sera

Sera

 

[sera]

Sorry bborissov...I didn't read the whole message.

I dunno what you mean by...create a repro code?

I am really looking for someone to tell me this has happened to them before...and this is why. It really is as simple as my explanation I promise.

The first example returns 24 records
The second example returns 2 records.


Sera

 

[sera]

Is it possible that I am declaring the @avar incorrectly?

DECLARE @avar As varchar

Sera

Sera

 

[bborissov]

Just create a code where we can see that error:
i.e. that code gives me exactly what I want:

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'abracadabra'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'some other thing'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'matchstring is something to find'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'We want just matchstring is something to find'[/color])

[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color]  @Test [COLOR=blue]WHERE[/color] Fld1 LIKE [COLOR=red]'matchstring%'[/color]

[COLOR=blue]DECLARE[/color] @tst [COLOR=blue]varchar[/color](200)
[COLOR=blue]SET[/color] @tst = [COLOR=red]'matchstring'[/color]
[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color]  @Test [COLOR=blue]WHERE[/color] Fld1 LIKE @tst + [COLOR=red]'%'[/color]

Could you mace the same just to show what the problem is?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[sera]

Declare @fileAbbr As varchar
Set @fileAbbr = 'PHEV1'
--returns 229 records
Select * From FileInfo Where FileName Like @fileAbbr + '%'
--returns 105 records
Select * From FileInfo Where FileName Like 'PHEV1%'

Sera

 

[sera]

FYI
I tried your example and it performed correctly....
Sera

Sera

 

[gmmastros]

This....

Declare @fileAbbr As varchar

Just for fun, open a new window and run this code.

[COLOR=blue]Declare[/color] @fileAbbr [COLOR=blue]As[/color] [COLOR=blue]varchar[/color]
[COLOR=blue]Set[/color] @fileAbbr = [COLOR=red]'abcdefghijklmnopqrstuvwxyz'[/color]

[COLOR=blue]Select[/color] @fileAbbr

You may be a bit surprised at the output.

To fix this....

Declare @fileAbbr As varchar[!](100)[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sera]

Whoa...it is the datatype....

When I do this it works

--note varchar is exactly the the same size as the string
Declare @fileAbbr As varchar(5)
Set @fileAbbr = 'PHEV1'
--returns 105 records
Select * From FileInfo Where FileName Like @fileAbbr + '%'
--returns 105 records
Select * From FileInfo Where FileName Like 'PHEV1%'

Sera

 

[bborissov]

Yes!!!!!
You answer to your post so maybe you need a star.
you define variable incorrectly. You ALWAYS have to add lenght of the variable (when it is required).
Try:

DECLARE @tst varchar
DECLARE @tst2 varchar(200)
SET @tst = 'matchstring'
SET @tst2 = 'matchstring'
SELECT @tst, @tst2

and you will see the difference


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[sera]

Well let that be a lesson to me....lazy coding style gets you lazy results....

Thanks Guys!

Sera

 

[bborissov]

BTW I was wrong in my first reply.
You found a way to get wrong results
I should remember this!
Thank you! (and I am serious here)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[sera]

Leave it to me to find wrong results...I am pretty good at that!


Sera

 

[bborissov]

Who isn't?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP