Path: csiph.com!optima2.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!1.eu.feeder.erje.net!news.swapon.de!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Regular expression question Date: Fri, 10 Jul 2015 21:54:42 +0200 Organization: Erland Sommarskog Lines: 43 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx02.eternal-september.org; posting-host="649a6bf9a71a2d72d51c0c898eded6c7"; logging-data="30141"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/HaoxdapxaUZw7rspR7eiy" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:BPCRdXC1oAb43mZWH9UHeJsJ9x4= Xref: csiph.com comp.databases.ms-sqlserver:1925 (rja.carnegie@gmail.com) writes: > SQL Server 2005. The second and third versions of the > following statement print 'Yes', which is unexpected. > > The intention is to test whether the first term is > a compound house "number" such as '1A' or '1-3'. > Either / or \ I thought would perform an "escape" > in the pattern string, to treat a following special > character as the literal character, but it seems to > perform correctly without that, not with. So this > test may be not doing what I think it is, at all. > And the question is: uh? ;-) > > IF ( '15' LIKE N'[1-9]%[-A-Z]%' ) PRINT 'Yes 1' > > IF ( '15' LIKE N'[1-9]%[/-A-Z]%' ) PRINT 'Yes 2' > > IF ( '15' LIKE N'[1-9]%[\-A-Z]%' ) PRINT 'Yes 3' > The patterns [/-A-Z] and [\-A-Z] means "all characters in the range from (back)slash to A, as well as hyphen and Z. This will match 15 if digits are in this range in your collation. There is no predefined escape character for LIKE, but you can specify one explictly: IF '15%' LIKE '[1-9][0-9]!%' ESCAPE '!' PRINT 'Yup' IF '15$' LIKE '[1-9][0-9]!%' ESCAPE '!' PRINT 'Nope' You can also use it inside brackets: IF '10%' LIKE '[1-9][0!-9]!%' ESCAPE '!' PRINT 'Yup' IF '11%' LIKE '[1-9][0!-9]!%' ESCAPE '!' PRINT 'Nope' IF '1-%' LIKE '[1-9][0!-9]!%' ESCAPE '!' PRINT 'Yup' IF '18%' LIKE '[1-9][0!-9]!%' ESCAPE '!' PRINT 'Nope' IF '19%' LIKE '[1-9][0!-9]!%' ESCAPE '!' PRINT 'Yup' -- Erland Sommarskog, Stockholm, esquel@sommarskog.se