concern on like operator

Question

Post reply

concern on like operator

DURGESH-769360

SSC Eights!

Points: 968
More actions
June 16, 2009 at 10:44 pm

#206029

Hi all,
I have tried the following query and didn't get the proper result that i am expected.
"select class_code from tbl_class where class_code like 'ep_f_%' "
Output:
-------
AP_F_SC
AP_F_VAL
AP_FFFB
AP_FFFH
But I have expected the result as
AP_F_SC
AP_F_VAL
Can anybody inform me whether I am correct or not
Regards
Durgesh

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Did you try to read BOL?

_____________
Code for TallyGenerator

Glenn Dorling SSCertifiable Points: 7878 More actions · Answer 2

Don't forget that the underscore character is treated as a a single-character wildcard, not an explicit character.

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

If you want the underscores to be treated as literals rather than wildcards, you need to escape them.

SELECT class_code FROM tbl_class WHERE class_code LIKE 'ep/_f/_%' ESCAPE '/'

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

Sergiy SSC Guru Points: 110208 More actions · Answer 4

GilaMonster (6/17/2009)
If you want the underscores to be treated as literals rather than wildcards, you need to escape them.
SELECT class_code FROM tbl_class WHERE class_code LIKE 'ep/_f/_%' ESCAPE '/'

You should be banned from SQL2000 forum. :laugh:

Your scripting habits way out of it. 🙂

I'd post compatible version but cannot access BOL now to copy-paste from there. 😉

_____________
Code for TallyGenerator

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Just tried on SQL Server 2000. ESCAPE works fine 😉

DECLARE @t TABLE (Txt VARCHAR(20))

INSERT INTO @t

SELECT 'AP_F_SC'

UNION ALL SELECT 'AP_F_VAL'

UNION ALL SELECT 'AP_FFFB'

UNION ALL SELECT 'AP_FFFH'

SELECT

*

FROM @t

WHERE Txt LIKE 'AP/_F/_%' ESCAPE '/'

Flo

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

Florian Reischl (6/17/2009)
Just tried on SQL Server 2000. ESCAPE works fine 😉

I was going to say...

I know I've used ESCAPE on SQL 2000 before...

http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx

LIKE:
Syntax
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_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

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Wow, never used it in 2000 ages. Considered it as 2k5 introduction.

Did not even try.

[_] always worked without any problem.

_____________
Code for TallyGenerator

LutzM SSC Guru Points: 107049 More actions · Answer 8

SET Humor ON

A query using "like 'ep_f_%'" shouldn't return any row starting with 'AP_'... 🙂

SET Humor OFF

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]