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

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

Re: Regular expression question

Show all headers | View raw

On Friday, 10 July 2015 20:54:45 UTC+1, Erland Sommarskog  wrote:
> (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'

I forgot to thank you for explaining this.

I wonder where I picked up the idea that you just use
a standard escape character.  Maybe it was described
and demonstrated in the next line, that I didn't read.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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