SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LIKE


LIKE

Author
Message
Joe Stefanelli
Joe Stefanelli
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 182
Toreador (6/24/2010)
A good idea, but a bit too easy to guess! Even though I failed to come up with an example, I got it right as the obvious answer was 'not possible', therefore for there to be any point asking the question, the answer had to be 'possible'!


Ditto here. :-)
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14264 Visits: 12197
Lynn Pettis (6/24/2010)
SanjayAttray (6/24/2010)
Even though I got it right and know its possible but when executed in SSMS 2005 it returned error.

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@a".


Code provided in the question is using SQL Server 2008 syntax, that's why it didn't work for you in SQL Server 2005 SSMS.


It's surprising how quickly I have come to use SQL 2008 syntax - if I'd written that just a few weeks earlier it would have been in SQL 2000 syntax (so it would have worked in 2000, 2005, and 2008).

Tom

Michael Poppers
Michael Poppers
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 416
OCTom (6/24/2010)
Sounds like an interview question. Hehe

At least as the first part of a possible two-parter ("So you think it's possible? OK, please give me an example....") :-).
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14264 Visits: 12197
Toreador (6/24/2010)
A good idea, but a bit too easy to guess! Even though I failed to come up with an example, I got it right as the obvious answer was 'not possible', therefore for there to be any point asking the question, the answer had to be 'possible'!

I wondered whether that reasoning would be common and perhaps lead to a success rate close to 100%. I thought that if the success rate looked too high I could devise a question where the obvious answer was the correct one for my next attempt and see if it attracted a high failure rate. But the success rate turne out to be reasonable - 246 people (about a third of those trying it so far) have got it wrong, so perhaps not too easy after all.

Tom

Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2766 Visits: 8083
I've probably been answering too many of these questions recently so always looking for the trick!
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2678 Visits: 2204
I figured it was possible because of trailing spaces, and tested it, but I messed my test rig up, so I got it wrong... Sad

Thanks for the question!
john.arnott
john.arnott
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1968 Visits: 3059
Lynn Pettis (6/24/2010)
SanjayAttray (6/24/2010)
Even though I got it right and know its possible but when executed in SSMS 2005 it returned error.

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@a".


Code provided in the question is using SQL Server 2008 syntax, that's why it didn't work for you in SQL Server 2005 SSMS.

Just to make the difference plain.... SQL 2005 requires a separate SET or SELECT to establish the value of a local variable.

declare @a varchar(6); set @a ='a ';
declare @b varchar(6); select @b = 'a';
if @a = @b and @a like @b and @b not like @a print 'zzz'


Richard Tatterton
Richard Tatterton
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 39
This is what I absolutely hate about SQL Server. A string is a f**king string, no matter whether it's got spaces on it at the end.

Jeez.
ziangij
ziangij
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3113 Visits: 376
good question ... thanks...:-)
Adam Haines
Adam Haines
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3174 Visits: 3135
Richard Tatterton (6/24/2010)
This is what I absolutely hate about SQL Server. A string is a f**king string, no matter whether it's got spaces on it at the end.

Jeez.


While this is true.... a string is a string, but the deviation here is the method used to evaluate the strings. Part of the ANSI standard is to make strings evaluated with "=" the same length before comparison and this is not a requirement for pattern matching. Like uses pattern matches so the string does not have to be equal.



My blog: http://jahaines.blogspot.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search