PATINDEX and '[' character

[John Winterbottom]

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.

[Erland Sommarskog]

John Winterbottom (jo***************@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

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" <jo*@bea.com> wrote in message
news:3F***************@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.

[John Winterbottom]

"Gert-Jan Strik" <so***@toomuchspamalready.nl> wrote in message
news:3F***************@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.

[John Winterbottom]

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

John Winterbottom (jo***************@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.