Interesting but Stupid Question

Question

Post reply

Interesting but Stupid Question

Bhuvnesh

SSC Guru

Points: 59351
More actions
August 13, 2008 at 11:19 pm

#96290

create table #Like_Test( Col1 varchar(5) )go
insert #Like_Test values('_Cus')
insert #Like_Test values('Cus_')
insert #Like_Test values('C_us')
insert #Like_Test values('Cus')
insert #Like_Test values('Cu_s')
--select * from #Like_Test
select * from #Like_Test where Col1 Like '%Cu_%'
and now tell me how many records are available for above select query.
Give reason of your reply.......
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Do we get a prize for answering correctly?

Remember that "_" is a single character wildcard in SQL, like "?" is in access.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 2

Actually, this looks like the question of the day from yesterday. Why re-post it here?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Now can i expect an answer from ..............experts......???

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 4

As Gail said the '_' character is a wildcard.

So if you wanted to only return the 1 record then the LIKE query would be:

select * from #Like_Test where Col1 Like '%Cu[_]%'

The above will only return 1 record by telling SQL that you actually want to search for the '_' by using the [_] inside the search string.

Hope this helps?

Thanks

Kevin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

bhuvnesh.dogra (8/17/2008)
Now can i expect an answer from ..............experts......???

What are you looking for? There was a good explanation in the newsletter for 14th Aug and there's a discussion in the question of the day thread

Oh, and what's your criteria for whether a person is an expert or not?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

hey ...how did it happen ? my question became QUESTION OF THE DAY..but without my name

? :unsure::unsure:

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 7

Not sure what you got here, but questions of the day are built 3-4 weeks ahead of time. Perhaps you saw the question early or it's a coincidence.

In any case, 4 rows returned.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

bhuvnesh.dogra (8/18/2008)
hey ...how did it happen ? my question became QUESTION OF THE DAY..but without my name
? :unsure::unsure:

Actually, it didn't. You posted this on the 14th Aug. It was question of the day for the 13th Aug. Hence you posted the question around 24 hours after it was sent to all subscribers in the newsletter.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JohnnyG-679899 SSC Enthusiast Points: 104 More actions · Answer 9

Not sure what you got here, but questions of the day are built 3-4 weeks ahead of time. Perhaps you saw the question early or it's a coincidence.

Coincidence... lol

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

That really is a stupid question, but not very interesting.

As for the answer, you can just run the code yourself to get that.

Krish-853432 SSC Veteran Points: 299 More actions · Answer 11

Hi,

create table #Like_Test( Col1 varchar(5) )

go

insert #Like_Test values('_Cus')

insert #Like_Test values('Cus_')

insert #Like_Test values('C_us')

insert #Like_Test values('Cus')

insert #Like_Test values('Cu_s')

--select * from #Like_Test

select * from #Like_Test where Col1 Like '%Cu_%'

This query produces 4 results except 'C_us'.

Thanks & Regards,
Krish.
(Together We WIN)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

can anyone tell how " _" is being treated in LIKE keyword. ??

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

bhuvnesh.dogra (8/19/2008)
can anyone tell how " _" is being treated in LIKE keyword. ??

A couple people already have.

As I said, back in my reply to your first post in this thread

Remember that "_" is a single character wildcard in SQL, like "?" is in access.

i.e. It matches any one character.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 14

please explain it in the context of below written script

create table #Like_Test( Col1 varchar(5) )

go

insert #Like_Test values('_Cus')

insert #Like_Test values('Cus_')

insert #Like_Test values('C_us')

insert #Like_Test values('Cus')

insert #Like_Test values('Cu_s')

GO

select * from #Like_Test where Col1 Like '%Cu_%'

How "Col1 Like '%Cu_%' " will work ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)