PATINDEX and [ character

Hi all

Using SQL Server 2000 SP3 on WIn2K Pro with SP2.

When I do this

SELECT PATINDEX('%[%', 'ABC[DEF')
GO

I get a result of 0, when I would expect a result of 4.

This works as I expect:

SELECT PATINDEX('%]%', 'ABC]DEF')
GO

I must be missing something really obvious here Any help would be
appreciated.John Winterbottom (john_winterbottom@.hotmail.com) writes:
> Using SQL Server 2000 SP3 on WIn2K Pro with SP2.
> When I do this
> SELECT PATINDEX('%[%', 'ABC[DEF')
> GO
> I get a result of 0, when I would expect a result of 4.

[ is meta-character in wildcard expressions. You need this:

SELECT PATINDEX('%[[]%', 'ABC[DEF')
GO

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The \ character is not an escape character until you define it as the
escape character. This is possible when using LIKE, but there is not
such ESCAPE clause in the PATINDEX syntax.

Therefore, you will need to resort to the second syntax, which is to
enclose it as the only character between square brackets.

SELECT PATINDEX('%[[]%', 'ABC[DEF')

Hope this helps,
Gert-Jan

John Winterbottom wrote:
> "Joseph Weinstein" <joe@.bea.com> wrote in message
> news:3F426D21.DB095501@.bea.com...
> > John Winterbottom wrote:
> > > Hi all
> > > > Using SQL Server 2000 SP3 on WIn2K Pro with SP2.
> > > > When I do this
> > > > SELECT PATINDEX('%[%', 'ABC[DEF')
> > > GO
> > > > I get a result of 0, when I would expect a result of 4.
> > > > This works as I expect:
> > > > SELECT PATINDEX('%]%', 'ABC]DEF')
> > > GO
> > > > I must be missing something really obvious here Any help would be
> > > appreciated.
> > Hi. I think the '[' is syntactically significant, and is parsed out of the
> input pattern,
> > just like the '%' is. Try escaping the '[' like this:
> > SELECT PATINDEX('%\[%', 'ABC[DEF')
> > This is just as if you wanted to search a pattern for the literal '%', you
> would
> > have to escape that... Lemme know...
> > Joe
> Thanks for the help Joe. Still doesn't work, even with the escape character.
> Yes, i think you are right - it must be interpreting the '[' character and
> stripping it out. If I come up with a fix I'll let you know.|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F42865B.C1FF0C4D@.toomuchspamalready.nl...
> The \ character is not an escape character until you define it as the
> escape character. This is possible when using LIKE, but there is not
> such ESCAPE clause in the PATINDEX syntax.
> Therefore, you will need to resort to the second syntax, which is to
> enclose it as the only character between square brackets.
> SELECT PATINDEX('%[[]%', 'ABC[DEF')
> Hope this helps,
> Gert-Jan

Perfect - thank you.|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93DCDFC6B245EYazorman@.127.0.0.1...
> John Winterbottom (john_winterbottom@.hotmail.com) writes:
> > Using SQL Server 2000 SP3 on WIn2K Pro with SP2.
> > When I do this
> > SELECT PATINDEX('%[%', 'ABC[DEF')
> > GO
> > I get a result of 0, when I would expect a result of 4.
> [ is meta-character in wildcard expressions. You need this:
> SELECT PATINDEX('%[[]%', 'ABC[DEF')
> GO

Thanks veyr much for your help. This works fine now.