Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1925

Re: Regular expression question

Show all headers | View raw

 (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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Regular expression question rja.carnegie@gmail.com - 2015-07-10 08:05 -0700
  Re: Regular expression question Erland Sommarskog <esquel@sommarskog.se> - 2015-07-10 21:54 +0200
    Re: Regular expression question rja.carnegie@gmail.com - 2015-07-18 08:59 -0700

csiph-web