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


LIKE operator


LIKE operator

Author
Message
Susantha Bathige
Susantha Bathige
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 282
Comments posted to this topic are about the item LIKE operator

Susantha
ChiragNS
ChiragNS
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: 14595 Visits: 1865
for a second i thought w00t.... nice question

"Keep Trying"
Jochen Vleming
Jochen Vleming
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 69
This one was quite simple (even for a n00b like myself BigGrin)
But a good try anyways...
A_A_R_T
A_A_R_T
Mr or Mrs. 500
Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)

Group: General Forum Members
Points: 574 Visits: 423
Nice one. To be honest: what I expected wasn't the right answer Wink But after testing it I knew what was Smile

Thanks for the lesson in wildcard characters!
jim.powers
jim.powers
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5296 Visits: 897
Tricky, tricky! Good question though. I fell into the trap probably because I haven't had enough coffee yet this morning. I saw the % signs and just completely forgot about the _ as a wildcard.
bhusank_neuronimbus
bhusank_neuronimbus
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 12
It was good question. I create confusion for underscore. I just like logical
GSquared
GSquared
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138701 Visits: 9731
You can also escape the wildcard characters with square-brackets.


where col like '[_]'


or

where col like '[%]'



- 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
The Dixie Flatline
The Dixie Flatline
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28495 Visits: 6919
Good question, and a learning experience for me

I missed it too, not realizing that the underscore wouldn't catch 'C_us'. The lesson learned is that underscore as a wildcard means that there MUST be some character, but % will accept the absence of a character. Proved this out by modifying your question a touch.


create table #Like_Test( Col1 varchar(5) )
go
insert #Like_Test values('_Cu')
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
where Col1 Like '%Cu_%'

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

select * from #Like_Test
where Col1 Like '%C%u%'

drop table #like_test

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Anipaul
Anipaul
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: 14565 Visits: 1407
Good Question ....Smile



sudhanva
sudhanva
SSChasing Mays
SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)

Group: General Forum Members
Points: 619 Visits: 241
create table #Like_Test( Col1 varchar(5) )
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 where Col1 Like '%Cu_%'

How can we really get the row with value as "Cu_" then?
(I mean the last record in the above table)

Something like this doesnt works..
select * from #Like_Test where Col1 Like '%[Cu_]%'
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